Pull records from last 24 hours in Crystal Reports

I need to pull medication administration records for the previous 24 hrs.  The Date and Time fields are separate and are in text form as 20110324 and 20:01.  What syntax do I need to put in the record selector so I can pull records from the previous 24 hours on demand?

Thank you.
Linda
LindaOKSTATEAsked:
Who is Participating?
 
mlmccCommented:
Apparently you have a bad time field
You can try

Not IsNull({YourDateField})
AND
Not IsNull({YourTimeField})
AND
(
IsTime({YourTimeField}) AND
DateTime(Date(Val(Left({YourDateField},4)), Val(Mid({YourDateField},5,2)), Val(Right({YourDateField},2)))+1,Time({YourTimeField})) >= CurrentDateTime
)

mlmcc

0
 
mlmccCommented:
Are you looking for the last full day or the 24 hours prior to the datetime of the run?

mlmcc
0
 
mlmccCommented:
For the last 24 hours you could use a selection formula like

DateTime(Date(Val(Left({YourDateField},4)), Val(Mid({YourDateField},5,2)), Val(Right({YourDateField},2)))+1,Time({YourTimeField})) >= CurrentDateTime

For the last full day
Date(Val(Left({YourDateField},4)), Val(Mid({YourDateField},5,2)), Val(Right({YourDateField},2)) = CurrentDate -1

mlmcc
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
LindaOKSTATEAuthor Commented:
I tried this one: DateTime(Date(Val(Left({YourDateField},4)), Val(Mid({YourDateField},5,2)), Val(Right({YourDateField},2)))+1,Time({YourTimeField})) >= CurrentDateTime

Time({YourTimeField}) --This part said "Bad time format string".  

I tried IsTime and it said "A Time is required here."

Any ideas?

Thanks
0
 
mlmccCommented:
You need to replace YourTimeField with the name of your time field.
Same with YourDateField

mlmcc
0
 
LindaOKSTATEAuthor Commented:
I did do that before I ran it.
0
 
mlmccCommented:
Can any of the fields be NULL?

Are they all good time fields?

mlmcc
0
 
LindaOKSTATEAuthor Commented:
There are some null fields.  These may be removed with further record selection but there are some there now.
0
 
mlmccCommented:
Then you need to test for them.  NULL is not a valid time.

If you want the records with a NULL date or time then use
IsNull({YourDateField})
OR
IsNull({YourTimeField})
OR
DateTime(Date(Val(Left({YourDateField},4)), Val(Mid({YourDateField},5,2)), Val(Right({YourDateField},2)))+1,Time({YourTimeField})) >= CurrentDateTime

If you don't want those records then
Not IsNull({YourDateField})
AND
Not IsNull({YourTimeField})
AND
DateTime(Date(Val(Left({YourDateField},4)), Val(Mid({YourDateField},5,2)), Val(Right({YourDateField},2)))+1,Time({YourTimeField})) >= CurrentDateTime

mlmcc
0
 
LindaOKSTATEAuthor Commented:
The report starts to run and then I get the same error:

Time({table.time})  
bad time format string

0
 
LindaOKSTATEAuthor Commented:
That seemed to do the trick.  Thank you very much!

LindaOKState
0
 
LindaOKSTATEAuthor Commented:
Very helpful, fast replies.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.