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?
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 ConnectWise

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