date range query

[emitecaps]

Hi,

I'ld like my database to collate a list of all bookings received in a particular date range. these would be collated weekly, but not on the same day each week.

I'ld like the user to select today's date - the query would then find the date for the Monday of this week, before finding all bookings with a booking date of between 2 and 3 weeks.

If I can't find a solution to this I can use a parameter query - but i'ld like to automate as much as possible.

thanks for your help

barry

 

[TomCologne]

Something like this:

SELECT tblCnt.CntDate
FROM tblCnt
WHERE (((tblCnt.CntDate) Between Date()-Weekday(Date())+2 And DateAdd("ww",-1,Date()-Weekday(Date())+2)));

Date()-Weekday(Date())+2 returns Monday of current week (US: first day=Sunday) and the DateAdd part the previous Monday.

Your user wouldn't even have to select today's date.


TomCologne

 

[PHV]

find the date for the Monday of this week
Date + 2 - DatePart("w", Date)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[emitecaps]

thanks guys..
am pretty new to this so please excuse my ignorance...

the table i'll be taking information from is called
"Bookings" and the date field is "Closing Date"

Would you just copy and paste what tom's suggested into the criteria box of a new query?
thanks again
barry

 

[TomCologne]

You'd need to copy & paste this

SELECT Bookings.[Closing Date]
FROM Bookings
WHERE (((Bookings.[Closing Date]) Between Date()-Weekday(Date())+2 And DateAdd("ww",-1,Date()-Weekday(Date())+2)));

into the SQL view of the query editor.

To test, open a new query, close the "Show table" dialog, the view button switches to SQL automatically, click the button, paste & run the query.


TomCologne

 

[emitecaps]

tom - that's wonderful..you've really helped me out!
one last thing (i promise) - this shows a range from last week to this week. Do you know how to tweak this to show a range from 2 to 3 weeks please?

a thousand thanks!

 

[emitecaps]

don't worry...have worked out how to do it. thanks again!