• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 854
  • Last Modified:

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.
0
aninec
Asked:
aninec
  • 5
  • 2
1 Solution
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

If it is a string then use

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

mlmcc
0
 
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now