Urgent: Date function help required for Access query

Tribble · Post by **Tribble** » 08 Jul 2007, 19:34

Please forgive me for interrupting you all on a Sunday night.

This is an easy question but my brain is no longer functioning.

Using an Access select query. In a date field I need to specify that only data of the current week must show (Monday to Friday). How do I do this - I am going mad trying to figure it out. As I say I know it is simple - just cannot think anymore. Will search the other forums in case there is no one here who can help.

A slightly harder question - how do I use the between [date] and [date] function in a crosstab query?

Thanks for any suggestions you can make.

Hex_Rated · Post by **Hex_Rated** » 08 Jul 2007, 20:17

Have a look at the datepart function:

WHERE datepart(wk,your_date_field_name) = datepart(wk,now()) -- makes records match current week number
AND datepart (dw, your_date_field_name) > 1 -- monday to
AND datepart (dw, your_date_field_name) <7 -- friday

Sorry, I have no idea if it will work.

Tribble · Post by **Tribble** » 08 Jul 2007, 20:22

Thanks I will try that. I appreciate the answer. Will let you know.

Tribble · Post by **Tribble** » 08 Jul 2007, 20:34

Solution (thanks to my friend Albert)

Create a column in the query with this as the field data

Code: Select all

Week: Format([CaptureDate],"ww")

And this as the criteria

Code: Select all

Format(Date(),"ww")

Thanks Hek Rated - never got to try your solution. Will do sometime and will let you know.