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

  • Thread starter Thread starter WDSnews
  • Start date Start date
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?
 
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...
 
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...
 
Back
Top