=Format([Hr],"hh:nnAM/PM") doesn't work when Hr is a string

[GraeagleBill]

=Format([Hr],"hh:nnAM/PM") doesn't work when Hr is a string. All the string values you see in the left column of the screenshot format as "12:00AM", whereas they should format as you see in the right column of the screenshot.



I could save Hr as Long seconds like the fractional portion of a Date format but I have the idea that it's simpler than that?

[ranman256]

you CAN use strings, but you cant use time format.
[timefield] MUST be numeric.


=format([timefield],"hh:nn AMPM")

[GraeagleBill]

BTW, I do have code that will to the formatting, like you see in the right column, but I'm thinking that the Format function can be used to simplify the job.

[andy49]

Timevalue function may be of use here to convert [hr] to a time.

[June7]

What screen shot?

[GraeagleBill]

How is your "timefield" different from my "Hr", i.e., both are strings?

[GraeagleBill]

Good grief!

[GraeagleBill]

I get a #Type error with =Format(TimeValue([Hr]),"hh:nnAMPM")

BTW, I'm using the expression you see above as the Format property of a text box.

[June7]

Can't use in Format property, use in ControlSource property.

[rpeare]

if your time is a string (0930 for 9:30 AM, 21:30 for 9:30 PM) you can do

Format(CDate(Left([hr],2) & ":" & Right([hr],2)),"hh:nnAM/PM")

[GraeagleBill]

Oops! I meant ControlSource, not Format.............. had "Format" on the brain!

[GraeagleBill]

That works. I'll have to add =IIF(isnull([Hr],null,Format(CDate(Left([hr],2) & ":" & Right([hr],2)),"hh:nnAM/PM")) so the text box formats properly when Hr is null.

[GraeagleBill]

Just needed to add the ")" on the IsNull function.

[June7]

It should still error because all parts of the IIf() must be able to evaluate, even the part that is not returned. This is different from an IIf() used in SQL statement.

So either do the IIf() calc in query or try:

=IIF(isnull([Hr],null,Format(CDate(Left(Nz([hr],"0000"),2) & ":" & Right(Nz([hr],"0000"),2)),"hh:nnAM/PM"))

Whoops, just tested your expression in textbox and it does not error. It is in VBA that the 'all parts must evaluate' applies.

[GraeagleBill]

Here's the form the produces "Hr". Works on the same principle as Allen Browne's Calendar form.