If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to return a 12 hour time format without the AM in an Access query
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? |
#2
|
|||
|
|||
How to return a 12 hour time format without the AM in an Access query
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" wrote in message ... 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? |
#3
|
|||
|
|||
How to return a 12 hour time format without the AM in an Access query
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" wrote in message ... 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|