between dates

[Guest]

Hi,
I am using a query to get records " Between #this date# and #that
date#"which is fine, but how can I write this query so I don't have to change
those dates each week.

 

[Guest]

Hi haggr,

If you use this (instead of what you have currently), it will capture the
last 60 days of records.

=Date()-60 And <=Date()

hth

rpw

 

[Jeff Boyce]

I don't understand what you are trying to do. If you wish to query between
two dates, but the dates are not somehow otherwise determined (say, "all of
last month", or "the two weeks preceding today"), won't you ALWAYS have to
specify them?

 

[Guest]

Between thursday and wednesday. Our work week. I need to be able to run the
query any time during that week ( or any week) and get a "current week to
date" result.

 

[Guest]

If I understand the request clearly, the query will always be between the
previous Thursday and today (whatever date today might be). So, if today is
Wednesday, the query is from Thursday last week thru Wednesday (today)
capturing 7 days worth of data. On this coming Friday, the query would be
from Thursday (tomorrow) thru Friday (2 days worth of data). Is all of this
correct?

In the meantime while I'm waiting for your answer, I'm going to poke around
in the help files for this. I'm sure that there is a way to specify
"Thursday" in Access or VBA, I just haven't used it yet myself.

 

[Guest]

Hi haggr,

There is probably lots of different ways of doing this, but this is what
I've figured out. Put the following code into a module in your db:

Function LastThurs() As Date
'This will find last Thursday's date
Dim strDates As Date
Dim intDay As Integer
intDay = Weekday(Date) 'Finds today's day of the week
Select Case intDay
Case 1 'Sunday!
strDates = Date - 3
Case 2 'Monday
strDates = Date - 4
Case 3 'Tuesday
strDates = Date - 5
Case 4 'Wednesday
strDates = Date - 6
Case 5 'Thursday
strDates = Date
Case 6 'Friday
strDates = Date - 1
Case 7 'Saturday
strDates = Date - 2
End Select
LastThurs = strDates
End Function

Then in the criteria for the date field of your query put this:

=LastThurs() And <=Date()

It works for my db, but I had to format the table's field to Short Date
before I got results.

hope this helps