Defining Time field format in the table

[louise]

Hello,

I have a time-of-day field that I need to display without am/pm and without a 24-hour clock.
I need to be able to sort on the field. (It is always a "day time" so sorting does not need am/pm)
And I need to be able to combine the field into a string with other information.

I am not sure how to define the original field in the table. Can I define it in this way right at the outset? Or must I to do it in every query, form, and report?

Thanks for any help.

[ranman256]

Time is ONLY stored 1 way. Its how you want to look at it.
am/pm
24 hrs
etc

it all sorts the same, it all adds the same, its just how you want to see it.
you can view it a number. (its actually a decimal)
format([timefld],"0.00000")

[ssanfu]

I am not sure how to define the original field in the table

In the table, just define it as a date/time field. No formatting.
It doesn't matter what it looks like in the table because users should never see/work with the table.
Same with queries.

In forms/reports, you (can) set formatting....... how you want it to look.

[louise]

ok, I understand that about the table.
But, can you tell me the format for simply hh:mm that won't convert afternoons to military time?

(Sorry if I should only send the reply once.)

Time is ONLY stored 1 way. Its how you want to look at it.
am/pm
24 hrs
etc

it all sorts the same, it all adds the same, its just how you want to see it.
you can view it a number. (its actually a decimal)
format([timefld],"0.00000")

[ssanfu]

The closest I have been able to come is

hh:nn - military time with two digits
h:nn a/p - non-military time with one or two hour digit as required and two digit minutes

In the format property I also tried:

=Left([TheTime],Len([TheTime])-6)

and

=CSTR(Left([TheTime],Len([TheTime])-6))

(change "TheTime" to your field name)
but I don't know if these will meet your other requirements.

You might have to use
h:nn a/p



Look at HELP. The hour defaults to military time.... adding "a/p" changes to non-military time but adds an "a" or "p" suffix.

[louise]

Hello,
This is posting from me is almost a year old, but I am still having trouble with the question of how to display time of day without AM/PM and without military time. (And without the tedious, ever-changing recoding that I have been using). I did as the immediately proceeding expert suggested: I put the expressions into the query (trying both suggestions) and they both do display as I had hoped. (Yeah!)
Here are examples of my two statements (StartTimeAMPM is the date/time field, StartingTime is the created field)

StartingTime: CStr(Left([StartTimeAMPM],Len([StartTimeAMPM])-6))

StartingTime: Left([StartTimeAMPM],Len([StartTimeAMPM])-6)

BUT, when I try to sort by either of these fields I get this error: "Data type mismatch in Criteria Expression." I see from the Database Documenter that they both become character strings, whether or not it is specified. So why wouldn't they sort as character string? (I realize that I wouldn't want to do this sort, since the times won't sort correctly, but I am worried about it not even being able to do it.)

Does this seem to be a good way to go? Should I leave the actual time-field in the query for the sort? Should I be doing this in a text box in each report, separately, instead of in the query? And, do you see any other issues in doing it this way?

I feel like I'm getting warm but still not there.
Thanks!

[ssanfu]

I would try leaving the field "StartTimeAMPM" in the query, moving that column to the far right, setting the sort order and unchecking the SHOW check box.
See if that is what you want.

[louise]

Thanks! I will do as you suggest.
Does it make a difference which of the two expressions (in my above message) I use?

[ssanfu]

The only difference I see is that the first one ensures the result will be a string