asked on

Converting String to military time when some fields are Null

My data:
125
1245
15
1920

I need to convert this to military time. However - the catch - some records have nothing entered for this field.

I have tried:
CTIME(mydata)
ToSTring(mydata)
ToTime(Mydata)

Under File/ReportOptions I have marked Convert Null Values to Default

Anything that I try is kicking an error Bad Time Format String. I think it has to do with the blanks. I want to leave the field blank.

Ultimately, I need to take two fields and calculate elapsed time.

SpeedFingolf

You should place a formula instead of the field. The formula should check for NULL or empty values.

Your formula could say:

If IsNull {MyTable.TimeField} then
""
Else
ToTime(MyTable.TimeField).

I really don't know if ToTime will give you the desired format, but I can assure that it will got give an error.

Mike McCracken

What do the numbers represent?

I am guessing they represent the time as an integer thus
125 is 01:25
15 is 00:15

Try this

If IsNull {MyTable.TimeField} then
""
Else
CStr({MyTable.TimeField},'0000')

mlmcc

aninec

ASKER

speed - that does not work - I get the same error

mlmcc - I get an error on the else statement - 'too many arguments have been given to this function'. It does not like the '0000'.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aninec

ASKER

The info now looks like Military time but it is not time to the report. I can not change the field formating and CTime does not work.

aninec

ASKER

I need the report to see this field as time. I have another field that i need to use to get elapsed time.

aninec

ASKER

I figured it out...given enough aggravation and head scratching - solutions do come. I am giving mlmcc the points because I found a solution based on another question that you answered prevously for someone else. I ended up doing a select statement in a view.

Thanks.

aninec

ASKER

If I could only download your brain to my computer!!!