Selection criteria for previous 24 hours 1

[tierdog]

I am using CR 8.0 with an Oracle 8 database and need the correct formula for pulling all activity for the previous 24 hours. The Date field and the time field are separate fields where the date field is a "date" field and the time field is a 4 character "string" field. Thus far, I am getting all activity for the previous date and the current date, not just from the current time forward on the previous date till now. Can you help?

 

[lbass]

Please show a samples of the time field and explain what the strings represent.

-LB

 

[tierdog]

The time field will be "0000" all the way through "2359

 

[tierdog]

As far as details go, I want to track the number of calls in any given 24 hour time period and then break the total up into different types of calls for service, if this makes any sense. The only thing is I don't want the 24 hour period to be a calendar day, I want it to be 24 hours from whatever time the report is run.

 

[lbass]

First convert the two fields into one datetime field {@datetime}:

{table.date} + time(val(left(table.time},2)),val(mid({table.time},3,2)),0)

Then you can use a formula like

{@datetime} >= dateadd("h",-24,currentdatetime)

-LB

 

[tierdog]

Thank you for the help. It appears to be working great!