Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2036
  • Last Modified:

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
0
LindaOKSTATE
Asked:
LindaOKSTATE
  • 6
  • 6
1 Solution
 
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now