Query based upon Now()

[sinyce]

Every day I generate a report based on a query which uses the Now()-1 (on the DATE field) to only give me the events for the previous day only.

Example of Table:

DATE TIME EVENTID USERID SEVERITY
7/7/2002 09:05 644 MIKEYJ LOCKOUT
7/8/2002 11:06 644 JACKOJ LOCKOUT
7/9/2002 09:28 644 JONESJ LOCKOUT
7/9/2002 09:48 644 TOMMYJ LOCKOUT

I have 2 problems: the Now() -1 equates to previous day with the start time when the report is run. e.g. if report is run at 10:20 a.m. on 7/10/2002 then it erroneously omits the 2 entries for 7/9/2002.

Is there a way to make the Now() or equivalent function to start from 12:00 a.m - 11:59 pm for the previous day?

If there is no entry for that day, print the report anyway but indicate that "NO LOCKOUT ENTRIES TO SYSTEM LOG" somewhere on the report?

Any help would be greatly appreciated.

 

[Guest_imported]

Hi,
I trust you are using a query to populate your report. Just put in two functions in the criteria section of your date field.

Criteria : Between start() And finish()

Now create a Module and add the following functions to the module.

Public Function start()
start = ((date - 1) & " 00:00:00 AM&quot
End Function

Public Function finish()
finish = (((date) - 1) & " 11:59:59 PM&quot
End Function

Please check if the timings are correct in the above functions. You should be able to get all records of the previous day.

Please reply if it works.

 

[CosmoKramer]

I've done something similar, but you don't have to create a module, you can do it right in the query. In the criteria of your date/time field, put code like this:

Between Date()-1 & " 00:00" And Date()-1 & " 23:59"

 

[sinyce]

Both methods work. Although I do not see the relavance for the & " 00:00". In the immediate window if I do:

?now() -1
7/9/2002 2:51:10 PM
?date() -1
7/9/2002

so in the query itself, i've used the
date()-1 - this works fine. Unless there is something I am missing here, because it returns all my data for that date, regardless of the time the report was run.

If I can add to this: if the current day fall on a monday I'd like to capture the logs for the entire weekend. Something like: >Day() -3

 

[LittleSmudge]

First point - use of "& 00:00" etc

It is not needed if you are using
= Date-1

However if you are using
BETWEEN DateA AND DateB then DateA needs to be the start of the day and DateB needs the end off the day.


Second Point - weekends
The problem with something like >Date()-3 is that you will also get all of TODAY's data entered by the 'early birds' before you run the report!

Use the WeekDay() function

Dim BDate As Date

If WeekDay(Date()) = 2 Then ' Weekday = Monday
BDate = Date()-3 & 00:00
Else
BDate = Date - 1 & 00:00
EndIf

then use
BETWEEN #" & BDate & "# AND Date()-1 & " 23:59:59"


'ope-that-'elps.

G LS