Previous 24 hours in a Report

[Scuda]

Hi guys,

A little while back, I created a combined "running log". All events that
happen within the Command Center are logged, and who logs it, as well as
time, and comments are recorded in a table.

I would love to be able to have a command button to "Print the Previous 24
Hours", or "Print Today's Log" that type of thing. How would I accomplish
this?

I currently have a query for picking a Month, Start Day and End Day for
Printing to a report, but would really love this.

Thanks in advance!
Steph

P.S. I did a search and couldn't find anything.

 

[Douglas J. Steele]

Rather than prompt for Month, Start Day and End Day, you can use Month(Date)
to get the current month, Day(DateAdd("d", -1, Date)) (or simply Day(Date -
1)) to get the Start Day and Day(Date) to get the End Day.

 

[Scuda]

Thanks Douglas! Is there any to specify current time MINUS 24 hours?

TA.

 

[John Spencer]

DateAdd("d",-1,Now()) or

DateAdd("n",-24,Now())

For example:

?Now() >>>
6/23/2007 3:30:11 PM

?DateAdd("d",-1,Now()) >>>
6/22/2007 3:30:11 PM

 

[Scuda]

Hi John, first off thanks for the response.

I put that in my query under the current_time field, and it returned
nothing. I tried the day one as well. I pasted my query SQl in the hopes of
maybe you can see something wrong? thanks again.

SELECT tblChronoLog.ChronoEntry, tblChronoLog.MONTH, tblChronoLog.DAY,
tblChronoLog.current_time, tblChronoLog.Watch
FROM tblChronoLog
GROUP BY tblChronoLog.ChronoEntry, tblChronoLog.MONTH, tblChronoLog.DAY,
tblChronoLog.current_time, tblChronoLog.Watch
HAVING (((tblChronoLog.current_time)=DateAdd("n",-24,Now())))
ORDER BY tblChronoLog.MONTH DESC;

 

[Scuda]

Hi again, I think I may have found the problem.

1) I added Is Not Null And >=DateAdd("d",-1,Now()) and it seemed to work
however it wasn't showing all of my entries for the previous day so I did
some digging.

2) I have the time automatically entered in using =(Now()) which records
fine. However if someone changes that time manually, it only records the
TIME, not the date, therefore my query results aren't showing it.

Well, I found the problem, now I need to find the solution! Back to the
drawing board. Thanks again!

 

[Douglas J. Steele]

The second alternative should be DateAdd("h",-24,Now())

DateAdd("n",-24,Now()) subtracts 24 minutes, not 24 hours.

 

[John Spencer]

First of all I had a typo.

DateAdd("h",-24,Now()) will give you 24 hours. "n" is minutes ("m" is
months.

Second I made the bad guess that you were storing the date and time in
one field. You seem to be storing the month in one field, the Day in
another, and finally the time in a third. Also, I don't know the data
types of any of those fields.

Assuming that
-- Month and Day are numbers
-- Current_Time is a dateTime field,
you might be able to do the following.

SELECT tblChronoLog.ChronoEntry, tblChronoLog.MONTH, tblChronoLog.DAY,
tblChronoLog.current_time, tblChronoLog.Watch
FROM tblChronoLog
WHERE DateSerial(Year(Date()),[Month],[Day])+tblChronoLog.current_time)

DateAdd("h",-24,Now())

GROUP BY tblChronoLog.ChronoEntry, tblChronoLog.MONTH, tblChronoLog.DAY,
tblChronoLog.current_time, tblChronoLog.Watch
ORDER BY tblChronoLog.MONTH DESC;

If the above doesn't work I suggest you post back with some more details
on your table structure.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================