Converting number to Time Format: Null Value's causing errors 1

[ColinBC]

Hi all,

I am working on a report for tracking productivity in our production area. The time field's I am using from our live data are in 8-digit number format. I have a formula below that works for converting the 7-8 digit number value to a date our {BVE_PHASE_DTL.START_TIME} field, however when I try to use this same formula for the {BVE_PHASE_DTL.END_TIME} field, it produces the error "Bad time format string." I am certain this is because there are Null Values in the {BVE_PHASE_DTL.END_TIME} tables. I don't mind if these null fields stay blank when the report runs as long as I don't get the "Bad time format string" error that prevents the report from running.

Here is an example of the formula that I am successfully using for the {BVE_PHASE_DTL.START_TIME} field:


StringVar sMyTime := ToText({BVE_PHASE_DTL.START_TIME}, 0, "");
IF Len(sMyTime) = 8 Then
cTime(sMyTime[1 to 2] & ":" & sMyTime[3 to 4] & ":" & sMyTime[5 to 6])
Else
cTime(sMyTime[1] & ":" & sMyTime[2 to 3] & ":" & sMyTime[4 to 5]);


This formula will display the numerical START_TIME field value of 18425770 as 6:42:57PM, or 8472173 will display as 8:47:21AM, however as soon as it encounters a null value I get the "Bad time format string" error.

If anyone can suggest a method to handle the null values without error, I'd be greatfull for the help and lesson!

Colin

 

[SkipVought]

Hi,

Something like this?

[pre]
StringVar sMyTime := ToText({BVE_PHASE_DTL.START_TIME}, 0, "");

If Len(sMyTime) <> "" Then
IF Len(sMyTime) = 8 Then
cTime(sMyTime[1 to 2] & ":" & sMyTime[3 to 4] & ":" & sMyTime[5 to 6])
Else
cTime(sMyTime[1] & ":" & sMyTime[2 to 3] & ":" & sMyTime[4 to 5]);
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColinBC]

Thanks for the suggestion. Unfortunately (I suspect because the START_TIME field is a number field) this formula produces the error "A number is required here" between the quotation marks in:

If Len(sMyTime) <> "" Then​

I was however able to expand on your suggestion and came up with a working formula, so thanks for the help! This is what worked:


StringVar sMyTime := ToText({BVE_PHASE_DTL.END_TIME}, 0, "");

If Len(sMyTime) < 1 Then time(23,59,59) else
IF Len(sMyTime) = 8 Then
cTime(sMyTime[1 to 2] & ":" & sMyTime[3 to 4] & ":" & sMyTime[5 to 6])
Else
IF Len(sMyTime) = 7 Then
cTime(sMyTime[1] & ":" & sMyTime[2 to 3] & ":" & sMyTime[4 to 5]);


***If I didn't also revise the 2nd to last line to include "IF Len(sMyTime)=7 Then" I was getting an "Invalid time string format" error. I will add a suppression rule to suppress any occurrences of the time 23:59:59 which is what will appear for null values.

Colin

 

[ColinBC]

Hmmm... Actually, my substitution in the above post doesn't appear to actually do anything. It was the "IF Len(sMyTime) = 7 Then " statement that looks to be the solution to the problem I was having

 

[SkipVought]

Duh!

I meant to type

If sMyTime <> "" Then

Not Len()...!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]