Pull records for the previous 24 hrs on demand.

Here is the previous question:

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?

I received this answer and I thought it was working.

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
)


However, I must have been at the end of the report so it looked like it was grabbing the records for the last 24 hrs.  Acutally it pulled all dates AND the Nulls.

Ideas?
LindaOKSTATEAsked:
Who is Participating?
 
mlmccCommented:
I don't know why it is but sometimes Crystal has trouble with complex formulas involving OR and AND

You might try this as

(
Not IsNull({TABLE.DATE})
and
Not IsNull({TABLE.TIME})
and
IsTime({TABLE.TIME})
and
DateTime(Date(Val(Left({TABLE.DATE},4)), Val(Mid({TABLE.DATE},5,2)), Val(Right({TABLE.DATE},2)))+1,Time({TABLE.TIME})) >= CurrentDateTime
)
AND
(
({TABLE.DRUG} like "HYDROCODO*")
or
({TABLE.DRUG} like "MORPHINE*")
or
({TABLE.DRUG} like "HYDROMORO*")
or
({TABLE.DRUG} like "FENTANYL*")
or
({TABLE.DRUG} like "NARCAN*")
)

In putting that together I noticed you have an extra ( after  the first OR.  Try removing it

mlmcc
0
 
mlmccCommented:
That should do it.

The Not IsNull should eliminate the NULL values

mlmcc
0
 
mlmccCommented:
Can you copy and paste the selection formula?

mlmcc
0
 
LindaOKSTATEAuthor Commented:
(({TABLE.DRUG} like "HYDROCODO*")
or
(({TABLE.DRUG} like "MORPHINE*")
or
({TABLE.DRUG} like "HYDROMORO*")
or
({TABLE.DRUG} like "FENTANYL*")
or
({TABLE.DRUG} like "NARCAN*"))and
Not IsNull({TABLE.DATE}) and
Not IsNull({TABLE.TIME}) and
IsTime({TABLE.TIME}) and
DateTime(Date(Val(Left({TABLE.DATE},4)), Val(Mid({TABLE.DATE},5,2)), Val(Right({TABLE.DATE},2)))+1,Time({TABLE.TIME})) >= CurrentDateTime)
0
 
LindaOKSTATEAuthor Commented:
Both the last two comment were useful, they both worked.
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.