Bad Time Format String 1

[newbie0423]

Hello All,

I am receiving a "bad time format string" error message when i run my report. I have narrowed the reason down to a time that is displaying as 0024 in the database. Can someone please help me with a code that will eliminate this error message. I am using CR XI

 

[fisheromacse]

how do other times display in your database?
is 0024 a valid time? ie: 12:24am?

 

[newbie0423]

they are stored as numbers. I am using the code below to covert to a time because i need to do a sum of the pat in room time and the pat out room time. this is the code that I gets the error message. When I look back at a particular record the patient went in the room at 0024

TimeValue (replace(left(CStr ({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room}),Length (cstr({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room}))-5),",","") & ":" & Left(Right(CStr ({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room}),5),2))

 

[CoSpringsGuy]

timevalue((tonumber({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room})/24)/60)

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

disregard that post

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[fisheromacse]

IF i am reading it correctly, i think this is (at least part) cause of your problems.

Length (cstr({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room}))-5

You are finding the length of the field and then subtracting 5, correct? if so, then in the example give, you have a number that is less than zero (4-5=-1), which is not a valid time value.

What time does 0024 represent?

what are the values of some other times in the database? ie: 104459, or 95312, or ????????

 

[newbie0423]

0024 represents 1224am (patient in room) some of the other time values are 1750 1023 759

 

[SkipVought]

I'm an Excel guy, but there are similarities.

It seems that you have time string values of the form hhmm. How about...
[tt]
TimeSerial(Left({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room},2),Right({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room},2),0)
[/tt]
or to make it more readable
[tt]
TimeSerial
(
Left({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room},2),
Right({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room},2),
0
)
[/tt]
and you might need to convert the results of the Left() & Right() functions to a number, as the TimeSerial() function wants the hour, minutes and seconds as numbers.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newbie0423]

When I try to run this i get a message saying that " a string is required here".
TimeSerial
(
Left({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room},2),
Right({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room},2),
0
)

 

[SkipVought]

Use cstr() to convert your variables to a string.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pmax9999]

I am a bit confused as to the actual data type of the {v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room} field. If it really is a number, 0024 would be stored as 24. Assuming it is the case, my approach is as follows:

Time(Left(Right('000' + ToText({Table.Time_field},'#'),4),2) + ':' + Right(Right('000' + ToText({Table.Time_field},'#'),4),2))

What this does is to convert the number (24) to text, pad out with leading zeros to 4 digits ("0024"), and separate the first 2 characters and the last two characters with a colon ("00:24"), and then converted to a time.

Hope this helps.

Cheers
Pete

 

[newbie0423]

Thanks a million for all! I REALLY appreciate the help. I used Pete's formula and it works perfectly! thanks again!