Pull records for last 24 hours, time field changed from 20:01 to 2001

Here is the previous question & the answer:

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?


(
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*")
)

********************************************************************

Now I need the same info but the colon is left out of the time.  So the date and time are still separate but
are now like this:

20110324 and 2001

I thought I could adjust the code to accomodate the missing colon but after much frustration I am reaching out and hope
that the code can be adjusted easily.

Thanks,
LindaOKState
LindaOKSTATEAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
Will the time always have 4 digits?  For example, would midnight be 0000 and 8:05 AM be 0805?  If so, you can just replace Time({TABLE.TIME}) with

Time(Left ({TABLE.TIME}, 2) + ":" + Right ({TABLE.TIME}, 2))

 All that that does is take the first 2 characters from the time and the last 2, and put a ":" in between them.  But it will only work if the time is always 4 digits (or 4 characters with a leading space, if necessary, like " 805", with a space at the beginning).

 If the time could be 3 characters (eg. "805" or "000") or 4 characters, you could replace Time({TABLE.TIME}) with

Time(
if Length ({TABLE.TIME}) = 3 then
  Left ({TABLE.TIME}, 1) + ":" + Right ({TABLE.TIME}, 2)
else
  if Length ({TABLE.TIME}) = 4 then
    Left ({TABLE.TIME}, 2) + ":" + Right ({TABLE.TIME}, 2)
  else
    "00:00")

 If the time is 3 characters, it puts a ":" after the first character (digit).  If the time is 4 characters, it puts a ":" after the second character (digit).  And, just in case, if the time is not 3 or 4 characters long, it ignores it and uses 00:00 (midnight) as the time instead.

 James
0
LindaOKSTATEAuthor Commented:
This is what I am running but I get no records.  If I take out the time references I get records from 24 hours ago and into the future.  Any ideas?

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(Left ({table.time}, 2) + ":" + Right ({table.time}, 2))) >= CurrentDateTime
0
mlmccCommented:
How is this true without the : in the field?
IsTime({table.time})

mlmcc
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

LindaOKSTATEAuthor Commented:
this works for the table where the time has no colon.  it gives records for the past 24 hrs
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
mlmccCommented:
Ar ethere "future" dated records in the database?

If so then try it this way

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

DateTime(Date(Val(Left({table.DATE},4)), Val(Mid({table.DATE},5,2)), Val(Right({table.DATE},2)))+1,Time({table.TIME})) <= CurrentDateTime + 1

mlmcc



0
LindaOKSTATEAuthor Commented:
I do not see the change for the colon in this last response so still no records.  I had to switch tables and the table I am using now has a colon in the time field.
0
LindaOKSTATEAuthor Commented:
I think there are two trains of thoughts going on here,  The answer to the first question worked because the time field had no colon.  I needed a new answer because I had to switch tables and the new table has a colon in the time field.  So, when I said this works for the time field with no colon:
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


I was meaning that there is no problem with this one, i need syntax to incorporte the colon.  The answer James gave incorporates the colon but I didn't get any records. When I took out the references to the time using his answer, I got records but they had future records also.
0
mlmccCommented:
Your original question here was for NO COLON.

mlmcc
0
mlmccCommented:
I just tested

In my version of Crystal Time("20:01") returns a good time - 20:01

Time("2001") an error for bad time format.

mlmcc
0
LindaOKSTATEAuthor Commented:
yes the original question was for no colon.  this is a related question.  This is from the first post of this question.

"Now I need the same info but the colon is left out of the time.  So the date and time are still separate but
are now like this:

20110324 and 2001

I thought I could adjust the code to accomodate the missing colon but after much frustration I am reaching out and hope that the code can be adjusted easily.'

I am not sure what you mean by " Time("20:01") returns a good time - 20:01

Time("2001") an error for bad time format"


0
mlmccCommented:
Try

Not IsNull({table.DATE}) and

Not IsNull({table.TIME}) and

IsTime(Left({table.TIME},2) & ":" & Right({table.TIME},2) and

DateTime(Date(Val(Left({table.DATE},4)), Val(Mid({table.DATE},5,2)), Val(Right({table.DATE},2)))+1,Time(Left({table.TIME},2) & ":" & Right({table.TIME},2)  >= CurrentDateTime
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LindaOKSTATEAuthor Commented:
it is giving error here:

Left({table.START_TIME},2) & ":" & Right({table.START_TIME},2)  

it says a date-time is required here.  it also wanted several extra parentheses at the end of the formula
0
mlmccCommented:
There is a ) missing at te end of that

Time(Left({table.TIME},2) & ":" & Right({table.TIME},2))

mlmcc
0
LindaOKSTATEAuthor Commented:
still getting the " date-time is required here" error
0
mlmccCommented:
Try adding 1 more ) at the end before the >=

mlmcc
0
LindaOKSTATEAuthor Commented:
Here is the whole statement.  This is working.  Thank you for your time and patience.

Not IsNull({table.date}) and
Not IsNull({table.time}) and
IsTime(Left({table.time},2) & ":" & Right({table.time},2) ) and
DateTime(Date(Val(Left({table.date},4)),
Val(Mid({table.date},5,2)),
Val(Right({table.date},2)))+1,
Time(Left({table.time},2) & ":" & Right({table.time},2))) >= CurrentDateTime

0
LindaOKSTATEAuthor Commented:
Not IsNull({table.date}) and
Not IsNull({table.time}) and
IsTime(Left({table.time},2) & ":" & Right({table.time},2) ) and
DateTime(Date(Val(Left({table.date},4)),
Val(Mid({table.date},5,2)),
Val(Right({table.date},2)))+1,
Time(Left({table.time},2) & ":" & Right({table.time},2))) >= CurrentDateTime
0
James0628Commented:
Ah.  Looking back at your original post, I see that I completely missed the fact that your formula included IsTime ({TABLE.TIME}).  I was only looking at where you converted the field with Time ({TABLE.TIME}).  As you've discovered, you have to also add the ":" to the field in the IsTime function, or that test won't be true and the whole thing fails.  Glad you worked it out.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.