Need to print the last 24 hours of records

[Claven]

I am using Access 2000.

I am trying to print a report of the last 24 hours of reports. This will be printed twice a day and needs to include the last 24 hours of reports. There could be one or several.

I created a report that included the information, then I decided that a query would be better. I was able to use the criteria Date() to get todays records, but this won't work for the AM as it will only include since midnight. How do I set the criteria to limit only for the last 24 hours? I have tried Date()-1 but that only shows the day before.

I was going to use a filter, but not sure how to get a date range?

Any ideas?

Dennis

 

[pbaldy]

Use the Between operator, Now() instead of Date() to include time, and the DateAdd function to subtract 24 hours. More info on all in VBA Help.

 

[JGT]

remember that a date is stored as a number, so to take 1 day off (24h) it is just date_-1. Look at this:

dummy_=#02/01/2008 10:00:00#
? Format((dummy_),"dd/mm/yyyy hh:mm:ss")
02/01/2008 10:00:00

dummy_=dummy_-1
? Format((dummy_),"dd/mm/yyyy hh:mm:ss")
01/01/2008 10:00:00

 

[Claven]

Well, I must have spent hours looking in the Access Help in the application. Where is VBA help located?

remember that a date is stored as a number, so to take 1 day off (24h) it is just date_-1. Look at this:

dummy_=#02/01/2008 10:00:00#
? Format((dummy_),"dd/mm/yyyy hh:mm:ss")
02/01/2008 10:00:00

dummy_=dummy_-1
? Format((dummy_),"dd/mm/yyyy hh:mm:ss")
01/01/2008 10:00:00

Ok, where do I place this code? I would like to use the criteria section of the query. So, would I place this in the criteria under the date. In the query the date called and the time called are seperate fields.


This is what a friend set up it to do....



Expr1: Hour([Response].[TimeCalled])


This is how it looks in sql view....

FROM Response
WHERE (((Response.DateCalled)=Date())) OR (((Response.DateCalled)=Date()-1) AND ((Hour([Response].[TimeCalled]))>=8));


This will give me the calls made from the previous 8pm. This works great.

Den

 

[namliam]

How about just simply going...
datecalled > now() - 1

 

[Claven]

Will that give me all of the current date and the previous date of records? If so, that is the ticket.

Thanks,

Den

 

[namliam]

If datecalled contains both time and date.... Yes...

Now() - 1 will at current GMT +1 (roughly) return Aug 20, 2008 15:01:12

Since you want all records > that... it should work.

 

[JGT]

Well all depends on how you use to work.
Lets suppose yr record table has a column “TimeStamp_” and you want to check the 24 hr period [Period_start … Period_end]
So the Where section of Query has to check TimeStamp_ against the upper and lower limits Period_end and Period_start)
eg TimeStamp > Period_start AND TimeStamp <= Period_end
(put the = wherever you need) or use the IIf function.
As Period_end = Period_start +1 or Period_start =Period_end-1 for the 24 hr period (depending if you prefer to go forward or backward in time) you can substitute one of the parameters. Suppose you use the end limit.
As it is an external value to the data set, you will have to bring the Period_end variable into the sql expression. The easiest way would be using now(), but I suppose your time period doesn’t always start/finish from now, so at some point your ap will have to request an input from the user to define the time period.
If you use VBA it would look like this:
Where TimeStamp <= #’ & Period_end & ‘# and TimeStamp > #’ & Period_end -1 & ‘#’, [Period_end] being a VBA variable of your code, that already got its value defined.
(Just take care you use the US notation for the time (mm/dd/yyyy) – otherwise add the format() function)
If you don’t use VBA but a Query you can put the variable as the "where" condition and the query will prompt the user to supply the external variable at execution time.
If you use a form to input the [Period_end] value, the easiest way I think would be to define a function lets say get_Period(), (get_Period = Period_end) that you can use in yr Query or Sql expression. Note that [Period_end] has to be a global variable.

 

[Claven]

Well, in the db the timecalled and datecalled are two separate fields. However, the datecalled is automatically set on open so it would contain the complete date and time. I don't use all the data for the forms and reports.

Den

 

[Claven]

BTW, I used

Date() -1

That only gave me records for the previous date, and not the current date. I will try your suggestion when I get back to work to look.

Den

 

[namliam]

Date() doesnt include the time... Now includes the time...

Why for the %()@^*@$)#*%^#@+_!@#%+_#%^_*^&%)(%^@#%&*(# hell would you split the time from the date??? This causes issues you dont want to know about. Like this S***....

Access only has one datatype Date/time, no seperate Date no seperate time. You are just 1) Making things hard on yourself 2) Eating up space 3) Making things hard on yourself.

IF your timecalled actually only contains only the time and datecalled actually only contains a date.... then try this on for size:
Datecalled + TimeCalled > Now () -1

Adding back together the ever powerfull combination of date and time to compare to a simulare date/time combination.

@JGT
I think it wouldnt hurt if you used an alinea or something alike... wow man what a hard wall of text to get thru

 

[neileg]

Well, I must have spent hours looking in the Access Help in the application. Where is VBA help located?

Open a VBA window, say a module or the code window of a form. VBA help is then available.

 

[Claven]

namliam,

With this database the datecalled is set, while we enter the time called as this is the time we are paged or called for an event. We then go to the medical emergency and deal with the event. When we get to charting the event we place the time paged and arrival time. We can't just use the time stamp when we open the db as this would not be the correct time we were actually paged and arrived. We track the time paged and time it takes to arrive to an event.

I do understand keeping the data of time and date together, but in this instance it is not the best option. So, the code we have come up with does do the trick and I do thank you all for your quick replies. I have found this a wonderful resource.


Thanks,

Dennis

 

[namliam]

OK that may be a valid reason to split up date and time... tho there are few...
Like what happens to a call that comes in on 21-aug-2008 23:55 (only date is stored appearently?) and you are paged 00:05 ??
Now if you have time only with the date only you end up beeing paged 21-aug-2008 00:05 ?? Neet you invented time travel

This is my point... you have to be carefull, real carefull, that is REAL carefull when taking these two components appart and out of context.