Changing a Query Criteria Automatically

[ChiTownDiva]

I have to run a daily report that pulls from yesterdays data. Can I write a module that will automatically change the date to the previous day? When Monday rolls around how do I Friday's data and not Sunday and Monday?

I figure once I can reformat my daily report, the monthly will have the same concept.

Thanks.

 

[DougP]

I created these two functions for just that.

Function LastMonday ()

Year1 = Year(Now)
Month1 = Month(Now)
Day1 = Day(Now)
'Debug.Print Year1, Month1, Day1
LastWeek = Format(Now, "WW&quot - 1

DayOWeek = Format(Now, "w&quot
'Debug.Print LastWeek, DayOWeek
Select Case DayOWeek
Case 1
Num = 6
Case 2
Num = 7
Case 3
Num = 8
Case 4
Num = 9
Case 5
Num = 10
Case 6
Num = 11
Case 7
Num = 12
End Select
LastMonday1 = DateSerial(Year1, Month1, Day1 - Num)
LastMonday2 = LastMonday1
Debug.Print "LastMonday1 "; LastMonday1
LastMonday = LastMonday1
End Function

------------------------------
Function LastFriday ()
Year1 = Year(Now)
Month1 = Month(Now)
Num3 = 4
LastFriday2 = DateSerial(Year1, Month1, Val(Format$(LastMonday2, "dd&quot) + Num3)
Debug.Print "LastFriday2 "; LastFriday2
LastFriday = LastFriday2
End Function
------------------------------

PS also add this to a module in the Declares

Global LastMonday2

I uesd it like so in a Date field in my query.

Between LastMonday AND LastFriday

So if todays is Jan 24th 2001 it would return
01/15/01 for last Monday and 01/20/01 for last Friday's dates
The nice thing about it, is that it will work on anyday of the week after that week. So if a holiday is on Monday and you can't print the report until Tuesday then it still works great and still gives you the correct dates.

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[JerryDennison]

In defference to Doug's rather convoluted method you might rather try putting this in your criteria Field instead.

IIf(Format(Now(),"ddd&quot="Mon", Now()-3, Now()-1)

 

[ChiTownDiva]

Jerry, the date field is a Date/Time field in the following format:

1/24/2001 12:35:00 PM.

When I tried the "IIf(Format(Now(),"ddd&quot="Mon", Now()-3, Now()-1)" in the criteria, the report came back blank.

What next? (You have to excuse my ignorance, but I'm not a programmer...at least not yet!!!)

Thanks.

 

[JerryDennison]

No problem. Just format both the criteria AND the date field to match.

Format([DateField], "Short Date&quot

Format(IIf(Format(Now(),"ddd&quot="Mon", Now()-3, Now()-1), "Short Date&quot

 

[ChiTownDiva]

Hi, Jerry.

I copied and pasted your last suggestion as a criteria in my query and it came back blank.

The table is linked (via OBDC) from a SQL Server table. In Access, the Date/Time format on the design view is blank.


Thanks!

 

[JerryDennison]

Without using the criteria insure that a record exists for the date you're trying to return. Is it possible no entries were made the prior week?