DateAdd Problem (1 Viewer)

[forms_are_nightmares]

Hello All,

I've created a query that includes a date field (the field is formatted in the table as date/time).

The idea is when the query is run it will only pull records that are 7 days before the current day, e.g., if the query is run on the 12th, it will only show records between the 12th and 5th of the month.

In the query, I added the following code: DateAdd("d",-7,Now())

This doesn't produce any results (there are records in the table that should be returned).

I'm hoping someone either might know what I'm doing wrong or can provide a better solution.

Thanks...

 

[JANR]

criteria for your date field:

Between DateAdd("d", -7, Date()) And Date()

JR

 

[forms_are_nightmares]

Thanks JR for the quick response.

It still comes up with no records. I'm not sure what the purpose of the Between statement is, though it may be that i'm not familiar enough with access.

 

[vapid2323]

Thanks JR for the quick response.

It still comes up with no records. I'm not sure what the purpose of the Between statement is, though it may be that i'm not familiar enough with access.

The reason for it would be to return all data for the last week, without it you would only return dates from 7 days back, and nothing from 6 to 0 days back.

 

[forms_are_nightmares]

Thanks for the clarification.

However, it still won't produce any records, even though I know there should be some.

Any ideas as to what is causing the issue?

 

[boblarson]

Post the SQL for your query.

 

[forms_are_nightmares]

SELECT tbl_priority_sheet.PSpecialist, Count(tbl_store_WO.WO) AS CountOfWO, tbl_store_WO.CDate
FROM tbl_priority_sheet INNER JOIN tbl_store_WO ON tbl_priority_sheet.ID = tbl_store_WO.ID
WHERE (((tbl_priority_sheet.Approve)=Yes))
GROUP BY tbl_priority_sheet.PSpecialist, tbl_store_WO.CDate
HAVING (((tbl_store_WO.CDate) Is Not Null));

 

[boblarson]

1. I don't see any DateAdd in there at all.

2. You are using an INNER JOIN between tbl_store_WO and tbl_priority_sheet. If there aren't matching records in the tbl_priority_sheet table for the particular tbl_store_WO then it will not show any records.

Also, is ID in one of the tables the FOREIGN KEY from the other and not the primary key in both? I wouldn't use just ID as the name. Use

PrioritySheetID

or

StoreID

so that it is clear what is what.

 

[forms_are_nightmares]

Bob,

Sorry, I was playing around with the code and didn't send the correct version:

SELECT tbl_priority_sheet.PSpecialist, Count(tbl_store_WO.WO) AS CountOfWO, tbl_store_WO.CDate
FROM tbl_priority_sheet INNER JOIN tbl_store_WO ON tbl_priority_sheet.ID = tbl_store_WO.ID
WHERE (((tbl_priority_sheet.Approve)=Yes))
GROUP BY tbl_priority_sheet.PSpecialist, tbl_store_WO.CDate
HAVING (((tbl_store_WO.CDate) Between DateAdd("d",-7,Date()) And Date()));

Let me see if I can address your other points-

The ID is the primary key in the main table and is related to the secondary table with referencial integrity. I know there are matching records in the secondary table.