How to return a 12 hour time format without the AM in an Access query

W

WDSnews

The format function acts differently in a query and after hours of testing,
messing, and cursing, I give up. How do you return 12 hour format without
the AM or PM from an Access query. In my table, I've defined the Start Time
and End Time to use "Medium Time" format and it works if and ONLY if you
simple include the field in the query. However, if you use the field in a
more complex statement, it returns leading zeros and trailing seconds.
Here's what I've tried....

'This didn't work because it returns trailing seconds and AM/PM.
'2:00:00 PM - 3:30:00 PM. I want 2:00 - 3:30.
Times: [Classes Proposed]![Start Time] & " - " & [Classes Proposed]![End
Time]

'This didn't work because it returns a 24H type hour.
Times: Format([Classes Proposed]![Start Time],"h:nn") & " - " &
Format([Classes Proposed]![End Time],"h:nn")

'This didn't work because it leaves a colon after the minutes for times when
the hour is a single digit.
Times: Trim(Left([Classes Proposed]![Start Time],5)) & " - " &
Trim(Left([Classes Proposed]![End Time],5))

'This doesn't work because it returns a leading zero in the hour.
Times: Trim(Left(Format([Classes Proposed]![Start Time], "medium time"), 5))
& " - " & Trim(Left(Format([Classes Proposed]![End Time], "medium time"),
5))

'This didn't work because it returns a 24H type hour.
Times: Trim(format([Classes Proposed]![Start Time],"h")) & ":" &
trim(format([Classes Proposed]![Start Time],"nn")) & " - " &
Trim(format([Classes Proposed]![End Time],"h")) & ":" & Trim(format([Classes
Proposed]![End Time],"nn"))

'This didn't work because it returns a 24H type hour.
Times: Trim(DatePart("h",[Classes Proposed]![Start Time])) & ":" &
Trim(Format([Classes Proposed]![Start Time],"nn")) & " - " &
Trim(DatePart("h",[Classes Proposed]![End Time])) & ":" &
Trim(Format([Classes Proposed]![End Time],"nn"))

'This didn't work because it return a single minutes digit for times on the
hour.
'14:0 - 15:30
Times: Trim(datepart("h",[Classes Proposed]![Start Time])) & ":" &
trim(datepart("n",[Classes Proposed]![Start Time])) & " - " &
Trim(datepart("h",[Classes Proposed]![End Time])) & ":" &
Trim(datepart("n",[Classes Proposed]![End Time]))

'This didn't work because it doesn't account for hours between noon and
10PM.
Times: Trim(Left([Classes Proposed]![Start Time],iif([Classes
Proposed]![Start Time]<#10:00#,4,5))) & " - " & Trim(Left([Classes
Proposed]![End Time],iif([Classes Proposed]![End Time]<#10:00#,4,5)))

So I wonder what will work?
 
D

Douglas J. Steele

Try:

Times: (Hour([Classes Proposed]![Start Time]) Mod 12) & ":" &
Format([Classes Proposed]![Start Time],"nn") & " - " &
(Hour([Classes Proposed]![End Time]) Mod 12) & ":" &
Format([Classes Proposed]![End Time],"nn")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
(no e-mails, please!)



WDSnews said:
The format function acts differently in a query and after hours of
testing, messing, and cursing, I give up. How do you return 12 hour
format without the AM or PM from an Access query. In my table, I've
defined the Start Time and End Time to use "Medium Time" format and it
works if and ONLY if you simple include the field in the query. However,
if you use the field in a more complex statement, it returns leading zeros
and trailing seconds. Here's what I've tried....

'This didn't work because it returns trailing seconds and AM/PM.
'2:00:00 PM - 3:30:00 PM. I want 2:00 - 3:30.
Times: [Classes Proposed]![Start Time] & " - " & [Classes Proposed]![End
Time]

'This didn't work because it returns a 24H type hour.
Times: Format([Classes Proposed]![Start Time],"h:nn") & " - " &
Format([Classes Proposed]![End Time],"h:nn")

'This didn't work because it leaves a colon after the minutes for times
when the hour is a single digit.
Times: Trim(Left([Classes Proposed]![Start Time],5)) & " - " &
Trim(Left([Classes Proposed]![End Time],5))

'This doesn't work because it returns a leading zero in the hour.
Times: Trim(Left(Format([Classes Proposed]![Start Time], "medium time"),
5)) & " - " & Trim(Left(Format([Classes Proposed]![End Time], "medium
time"), 5))

'This didn't work because it returns a 24H type hour.
Times: Trim(format([Classes Proposed]![Start Time],"h")) & ":" &
trim(format([Classes Proposed]![Start Time],"nn")) & " - " &
Trim(format([Classes Proposed]![End Time],"h")) & ":" &
Trim(format([Classes Proposed]![End Time],"nn"))

'This didn't work because it returns a 24H type hour.
Times: Trim(DatePart("h",[Classes Proposed]![Start Time])) & ":" &
Trim(Format([Classes Proposed]![Start Time],"nn")) & " - " &
Trim(DatePart("h",[Classes Proposed]![End Time])) & ":" &
Trim(Format([Classes Proposed]![End Time],"nn"))

'This didn't work because it return a single minutes digit for times on
the hour.
'14:0 - 15:30
Times: Trim(datepart("h",[Classes Proposed]![Start Time])) & ":" &
trim(datepart("n",[Classes Proposed]![Start Time])) & " - " &
Trim(datepart("h",[Classes Proposed]![End Time])) & ":" &
Trim(datepart("n",[Classes Proposed]![End Time]))

'This didn't work because it doesn't account for hours between noon and
10PM.
Times: Trim(Left([Classes Proposed]![Start Time],iif([Classes
Proposed]![Start Time]<#10:00#,4,5))) & " - " & Trim(Left([Classes
Proposed]![End Time],iif([Classes Proposed]![End Time]<#10:00#,4,5)))

So I wonder what will work?
Click to expand...
 
W

WDSnews

cool. Thank you. After I posted that last message, I thought of one other
approach that turned out successful, but not as elegant as your suggestion:

Times: iif(Mid([Classes Proposed]![Start Time],2,1)=":",left([Classes
Proposed]![Start Time],4),left([Classes Proposed]![Start Time],5)) & "-" &
iif(Mid([Classes Proposed]![End Time],2,1)=":",left([Classes Proposed]![End
Time],4),left([Classes Proposed]![End Time],5))






Douglas J. Steele said:
Try:

Times: (Hour([Classes Proposed]![Start Time]) Mod 12) & ":" &
Format([Classes Proposed]![Start Time],"nn") & " - " &
(Hour([Classes Proposed]![End Time]) Mod 12) & ":" &
Format([Classes Proposed]![End Time],"nn")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
(no e-mails, please!)



WDSnews said:
The format function acts differently in a query and after hours of
testing, messing, and cursing, I give up. How do you return 12 hour
format without the AM or PM from an Access query. In my table, I've
defined the Start Time and End Time to use "Medium Time" format and it
works if and ONLY if you simple include the field in the query. However,
if you use the field in a more complex statement, it returns leading
zeros and trailing seconds. Here's what I've tried....

'This didn't work because it returns trailing seconds and AM/PM.
'2:00:00 PM - 3:30:00 PM. I want 2:00 - 3:30.
Times: [Classes Proposed]![Start Time] & " - " & [Classes Proposed]![End
Time]

'This didn't work because it returns a 24H type hour.
Times: Format([Classes Proposed]![Start Time],"h:nn") & " - " &
Format([Classes Proposed]![End Time],"h:nn")

'This didn't work because it leaves a colon after the minutes for times
when the hour is a single digit.
Times: Trim(Left([Classes Proposed]![Start Time],5)) & " - " &
Trim(Left([Classes Proposed]![End Time],5))

'This doesn't work because it returns a leading zero in the hour.
Times: Trim(Left(Format([Classes Proposed]![Start Time], "medium time"),
5)) & " - " & Trim(Left(Format([Classes Proposed]![End Time], "medium
time"), 5))

'This didn't work because it returns a 24H type hour.
Times: Trim(format([Classes Proposed]![Start Time],"h")) & ":" &
trim(format([Classes Proposed]![Start Time],"nn")) & " - " &
Trim(format([Classes Proposed]![End Time],"h")) & ":" &
Trim(format([Classes Proposed]![End Time],"nn"))

'This didn't work because it returns a 24H type hour.
Times: Trim(DatePart("h",[Classes Proposed]![Start Time])) & ":" &
Trim(Format([Classes Proposed]![Start Time],"nn")) & " - " &
Trim(DatePart("h",[Classes Proposed]![End Time])) & ":" &
Trim(Format([Classes Proposed]![End Time],"nn"))

'This didn't work because it return a single minutes digit for times on
the hour.
'14:0 - 15:30
Times: Trim(datepart("h",[Classes Proposed]![Start Time])) & ":" &
trim(datepart("n",[Classes Proposed]![Start Time])) & " - " &
Trim(datepart("h",[Classes Proposed]![End Time])) & ":" &
Trim(datepart("n",[Classes Proposed]![End Time]))

'This didn't work because it doesn't account for hours between noon and
10PM.
Times: Trim(Left([Classes Proposed]![Start Time],iif([Classes
Proposed]![Start Time]<#10:00#,4,5))) & " - " & Trim(Left([Classes
Proposed]![End Time],iif([Classes Proposed]![End Time]<#10:00#,4,5)))

So I wonder what will work?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time Trouble - Data Type, Query, Comparison 2
display time in 12 hour format 1
How to enter start and end date/time in excel based on some specific value. 7
How to validate time format from a text field? 2
Crosstab Query, Group & Sort by Hour in the day 2
How many are at work every hour in a 24-hour day? 2
Adding Daily Time On Duty In a Query 6
How do I get the result of the query in "short time" format? 2

Top