Where Statement using Between is Not filtering out Records

[quest4]

Good afternoon, I have a query and on the keying off of the DateCreated I am trying to create a query for a report for the last weeks entries and I am using a WHERE in the sort and in the criteria I have tried these two conditions and neither one will filter out the 6/1/08 record.

Between Date()-Weekday(Date(),1)-"5" And Date()-Weekday(Date(),1)+"1"

Between (Date()-(Weekday(Date())+364)) And (Date()+(7-Weekday(Date())))

Both print out all of the records and 1 record is for 6/1/08 and the 2 are for 7/1/08. Any suggestion as to what I am doing wrong? Thank you in advance for any assistance.

 

[boblarson]

I would suggest using the date add function but what are you really trying for here?

Also, you can't ADD or SUBTRACT strings so why are there quotes around 5 and 1 ("5" and "1")?

 

[quest4]

Thanks Bob for the response. I am trying to use the querry to print out a report about what was entered last week. I did manage to get it working correctly but how right it is I am not yet sure. I used this expression in the query:
Not Between (Date()-(Weekday(Date())+364)) And (Date()-Weekday(Date()))
Now this seems to work through a couple of tries and I hope it continues to work. Thanks again for the response.

 

[datAdrenaline]

... Hmmm ... June 1 was more than a week ago, so I would not suspect it would be in the resultant set ...

....

For your criteria for LAST WEEK (I assume you mean Monday to Sunday) here is a progress to follow to get to the prior Monday and subsequent Sunday ...

To get to the CLOSEST Monday that has passed, the expression is ...

DateAdd("d", 1 - Weekday(Date(), 2), Date()) {note the 2 is the vb constant vbMonday}

Example: DateAdd("d", 1 - Weekday(#7/2/2008#, 2), #7/2/2008#) ==> 6/30/2008

Next ... you want to subtract a week from the closest monday that has passed ...

DateAdd("ww",-1,DateAdd("d", 1 - Weekday(Date(), 2), Date()))

Example: DateAdd("ww",-1,DateAdd("d", 1 - Weekday(#7/2/2008#, 2), #7/2/2008#)) ==> 6/23/2008

{Note you could also just subtract 7 from the first passed Monday}

Now that we have the start date ... we need to get stop date, which is the first Sunday that has passed us by, which is just like the first Monday that has passed, except with a different constant.

DateAdd("d", 1 - Weekday(Date(), 1), Date())

Example: DateAdd("d", 1 - Weekday(#7/2/2008#, 1), #7/2/2008#) ==> 6/29/2008

So ... the criteria for "Last Week" would be ....

Between DateAdd("ww",-1,DateAdd("d", 1 - Weekday(Date(), 2), Date())) And DateAdd("d", 1 - Weekday(Date(), 1), Date())

Hope that helps!!

 

[Brianwarnock]

Although you shouldn't have "5" and "1" the formula works quite happily and does filter out 1 June 2008 the dates created are 23/06/08 and 29/06/08 which is what you wanted I presume, so its a puzzle.

Brian

 

[quest4]

Thanks for the response Brent and so I copied the code and pasted it into the expression builder and then ran the query and nothing. The 2 records it should have found, it now can not find. I under stand what you said and in theory it should have worked, but it does not and I don't understand why? Thanks again for the help.

 

[datAdrenaline]

Can I see your full SQL statement for the query you are building? ... Is the field you are utilizing this criteria for a Date/Time field?

 

[quest4]

Thank you all for your responses. Brent here is the entire query:
SELECT DISTINCTROW tblDoctors.DoctorName_Last, tblDoctors.DoctorName_First, tblReportData.DateCreated, tblReportData.PatientNo, tblReportData.PatientName_First, tblReportData.PatientName_Last, tblReportData.TypeofReport, tblReportData.Status, Sum(tblReportData.CharacterTotal) AS [Sum Of CharacterTotal]
FROM tblDoctors INNER JOIN tblReportData ON tblDoctors.DoctorID = tblReportData.DoctorID
WHERE (((tblReportData.DateCreated) Between DateAdd("ww",-1,DateAdd("d",1-Weekday(Date(),2),Date())) And DateAdd("d",1-Weekday(Date(),1),Date())))
GROUP BY tblDoctors.DoctorName_Last, tblDoctors.DoctorName_First, tblReportData.DateCreated, tblReportData.PatientNo, tblReportData.PatientName_First, tblReportData.PatientName_Last, tblReportData.TypeofReport, tblReportData.Status, tblReportData.DoctorID
ORDER BY tblDoctors.DoctorName_Last;
Like I said this is just for a report of the all of the transcriptions per doctor for a couple of doctors done during the previous week, Monday thru Sunday would be idea and the report would be run on Monday morning. Basically it is a glorified log of the number of reports typed up. Thank you again for all of the help.

 

[quest4]

I must be going blind or just plain getting dumb. It is now working and working as you said. The date was outside of the parameter and that is why I got nothing with the query. Thanks alot and have a great day.

 

[datAdrenaline]

You're welcome! ... always glad to assist!