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

SpeedFingolfCommented:
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.
0
mlmccCommented:
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
0
aninecAuthor Commented:
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'.  
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

mlmccCommented:
Is the time field a string or a number?

If it is a string then use

   Right('0000' & {MyTable.TimeField},4)

mlmcc
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
aninecAuthor Commented:
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.
0
aninecAuthor Commented:
I need the report to see this field as time.  I have another field that i need to use to get elapsed time.
0
aninecAuthor Commented:
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.
0
aninecAuthor Commented:
If I could only download your brain to my computer!!!
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.