Last Wednesday

[travismp]

Here is my current code:

FROM tbl_COMPANY INNER JOIN tbl_TESTS ON tbl_COMPANY.CompName = tbl_TESTS.CompName
WHERE (((tbl_TESTS.DateBilled)=#3/5/2008 7:15:02 AM#))
ORDER BY tbl_TESTS.CompName;

I no longer want to refrence the exact date and time. How can I change this to look at the most recent Wednesday? So if today is Friday it will look back 2 days, if today is Monday it will look back 5 days, if today is Wed it will look at today. I need it to find the most recent Wed.

We have an update Query that bills updates a Date field every Wed. I then update this query to pull a few reports. I think there is a better way to do this right? Thanks.

 

[Brianwarnock]

You need to use ethe weekday function
Days are numbered0-7 Sun -Sat , thus Wednesday is 4

Weekday(Date())gives you the current day number
If >=4 subtract 4 to see how many days to go back else+4 to see how many to go back

ie Thursday returns 5 -4 =1
Tuesday returns 3 +4 =7
use the result in dateadd

Brian

 

[raskew]

Days are numbered0-7 Sun -Sat , thus Wednesday is 4

They are actually numbered 1 - 7, with 1 being Sunday.

Bob

 

[Brianwarnock]

I'm going daft in my old age

Brian

 

[raskew]

I hear you! There's a lot of that going around.

Best wishes -- Bob