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?



0
WDSnews
5/17/2010 9:29:01 PM
access 16762 articles. 2 followers. Follow

2 Replies
950 Views

Similar Articles

[PageSpeed] 29

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" <wdsnews.0640@oregoncity.com> wrote in message 
news:OpyE5fg9KHA.5412@TK2MSFTNGP06.phx.gbl...
> 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?
>
>
> 

0
Douglas
5/17/2010 11:24:13 PM
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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
news:eId4Lgh9KHA.3880@TK2MSFTNGP04.phx.gbl...
> 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" <wdsnews.0640@oregoncity.com> wrote in message 
> news:OpyE5fg9KHA.5412@TK2MSFTNGP06.phx.gbl...
>> 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?
>>
>>
>>
> 


0
WDSnews
5/19/2010 4:42:55 AM
Reply:

Similar Artilces:

Conditional Format for Numbers
Is it possible in Excel to conditionally format a Number type in a cell based on another cell? Example A1 is a dropdown with "Variance $" and "Variance %" And in A2 I have an IF statement that that will give a result from two other cells. What I want A2 to show is either $#.## or ##% depending on what I have in the drop down. Any help would be great. Thanks Tyson On 27 Jun 2005 12:36:59 -0700, "Tyson" <tmunee@hotmail.com> wrote: >Is it possible in Excel to conditionally format a Number type in a cell >based on another cell? > >Example &...

Font formatting in Active X Control boxes
I've created a form using Developer Contols Active X Controls, specifically a text box field. I've highlighted the entire document and set the font at Arial 12 pt. Some of the text box fields fill with Arial 12 pt. and some fill with Times New Roman 12 pt. How do I change the formatting so ALL the fields print using the same font and point size? Make sure Word is in Design mode. Right-click an ActiveX control and choose Properties. Set the desired font in the properties window. Note, however, that ActiveX controls aren't very easy to deal with. (For one thing, mac...

Time based entry
I was wondering. Is it possible to automate a table or field entry based on time criteria. I am creating a database where the user will have to designate a transaction "cleared "for the balance to be reflected correctly. Can I do this automatically to avoid human error. For example it takes 10 days from a transaction entry to clear, therfore after 10 days I would like it to be reflected automatically if the user has not manually made the adjustment. TIA You could try this using an update query that runs when the database is opened, something like: UPDATE yourtable SET yourtable....

Publisher
I am not sure if this post belongs in this newsgroup or in Access. I have not recieved an anser in the Access group so I will try it here. I have a document designed in Publisher 2003 that I want to do a merge with a query in Access 2003. When I choose the Access front end on the local computer to link to and test the connection I get a "Microsoft Data Link Error" window that states "Test onection failed because of an error initializing provider. Cannot start you application. The workgroup information file is missing or opened exclusively by another user." The Acce...

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Excel
1. An Excel tabulation begins at Row 6 ; and then, Cell G6 is subject to Conditional Formatting as follows :- =AND(ROWS($G$6:G6)<=COUNTIF($B$6:$B$35,$B$6)+COUNTBLANK($G$6:G6),G6<>"") 2. Subsequently, the Conditional Formatting of Cell G6 is "Paint-Brushed" to the succeeding cells. 3. And there, a blank row is inserted over Row 6 ; Look at the Conditional Formatting again, it has changed as follows :- =AND(ROWS($A$1:$G$7)<=COUNTIF($B$7:$B$36,$B$7)+COUNTBLANK($A$1:$G$7),G6<>"") 4. Now, is that change justifiably outrageous ? Expected, or what ...

Quicker Calculation Time
I have a sheet thath as multiple LOOKUP functions being used and couple custom functions being used. The calculations are extremely slow...is there a way to speed them up -- jeffcravene ----------------------------------------------------------------------- jeffcravener's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9 View this thread: http://www.excelforum.com/showthread.php?threadid=31977 Since you give no details, it's impossible to give you any specific advice. Check out: http://www.decisionmodels.com/optspeed.htm In article <jeffcravener....

Ten Key Calculator cell format
Is there a way to format a cell to move the decimal back two spaces similar to a ten key adding machine. In example, if I key 1025 in a cell can a custom format make that value 10.25. Other examples: Enter Format 10 .10 456 4.56 2000 20.00 Thanks in advance for anyone who could help me with this. Manny ...

VC++ 6.0 dll can not access a network drive
Hi All, I have a VC 6.0 dll, which is called from one another exe. This dll in turn will execute couple of programs in a network with CreateProcess. When I ran the CreateProcess, my getlast error returned with error code 3 (directory not found). I opened a dos box and I was able to switch to the network drive without any problem. I checked and made sure the user has full permission to the drive where he is trying to access the files. The program is running under vista OS. The server is Windows 2K3. I wrote a simple console application in VC 6.0, which does nothing but do a lookup on a file o...

Inserting Date/Time of Last Update
Is there a way to insert a date and time in a cell(s) that will only be updated if there is a change to the worksheet. I'm looking to have the date/time of the last update so that it is easy to tell how current the worksheet is. Thanks -- Lionel B. Dyck <>< AIM ID: lbdyck Yahoo IM: lbdyck Homepage http://www.lbdsoftware.com/ Blog: http://randommgmt.blogspot.com/ Lionel You could put some code in a worksheet_Change() event like so (uses A1) Private Sub Worksheet_Change(ByVal Target As Range) Range("A1").Value = Now() End Sub To implement right click the sheet t...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Remove Cell Auto-Format (General) on Paste / Replace
Good morning all, Just curious if this is able to be done. I have a series o spreadsheets, all of them do different things, but the common factor i that I paste claim numbers into them. The problem is, Excel seems t think our claim numbers are dates. (Example. 2146/04 or 04/262 (different formats for different sections). Now, I have formatted AL cells on these spreadsheets as text. (Ctrl-A, Format-Cells-Text However, when pasting, Excel overrides whatever you've got, and choose it's own way to paste data. The other problem is with Find-Replace or Find-Replace All. A lot o the time, ...

Report showing which users have access to a particular window/repo
Is there a report that can be printed (or a particular table that can be looked at) that will list all users that have access to a particular window or report? We're on GP 10, upgraded from GP 9, so all the roles and tasks are CNV_..... In particular, we need a quick and easy way to see which users have access the the payroll check history report. Sure there is. Abreviated instructions follow but detailed ones can be found in our Dynamics GP Security Handbook on page II-38. Load the Security Resource Table first by going to Maintenance, select Clear Tables (BE CAREFUL)...

Format cells unavailable
I am assisting a customer running Word 2000 on a Windows 2000 system. He has a spread sheet that will not allow him to do a format > cells on the header row and a few rows below. We can select the colum and this option works but nothing happens when using this option for an individual cell. I don't think these items are protected, is there any way I can be sure? I tried selecting all of the columns and unlocked the cells and this did not help. Can anyone think of any reason why format cells would not be available on this sheet? TIA George Tools, Protection. If the sheet's ...

emial receive time different than system time
hi! I have a problem with my email received time stamp being different than the system time. The time stamp shows two hours earlier than when it was originally sent. This is on a windows 2000 machine. I am going crazy! trying to organize my email by received time. Can someone help me! thanks! I'm not sure, but you might want to check with your ISP. The time on THEIR system may be two hours before yours so it's showing it was received two hours earlier. Open one of the messages and then click on View and then Options. Check out the Internet Headers section which shows how the e...

Access 2000 Upsizing Wizard with SQL Server 2000 Developer
I am getting the following message and I am not sure why. Connection failed: SQL State: '01000' SQL Server Error: 53 [Microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Open (Connect()). Connection failed: SQL State: '08001' SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not exist or access denied. I used the default settings to name the Server [local] and I double checked through Enterprise Manager that I could see the server and the databases. Any ideas on what is wrong? I am new to SQL Server. CLM In the newsgroup post, yo...

Counting how many times a number appears in a list?(EXCEL)
hi, got a small problem in excel, situation as below: grade 1 2 3 1 2 1 2 3 4 1 1 what formula do I use to count how many times (e.g) 1 appears in th list? I thought I could do it with the Count function but haven managed to. Cheers -- Message posted from http://www.ExcelForum.com The "countif" formula should do! Dunca -- Message posted from http://www.ExcelForum.com =COUNTIF(A1:A15,1) -- Regards, Peo Sjoblom "sox >" <<sox.152stn@excelforum-nospam.com> wrote in message news:sox.152stn@excelforum-nospam.com... > hi, got a small problem in excel...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

is there a way to change the time card repor format?
i'm using activereports in RMS to generate a custome timecard report. for my timein and timeout colums, the datatype i have assigned to the columns is vbDate. when I generate the report, the columns only show the date as mm/dd/yyyy, they do not show the time of day piece for exactly when the cashier punched in or out. is there any way to change the formatting of this so that in the report the time shows up? for example, instead of showing 2/6/2005 in the time in column, it would show 2/5/2005 8:00:00 AM. thanks in advance, kevin There is a timecard report on the Reports Library...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Can't update Office 2008 to 12.2.0
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, I've just purchased and installed a new Office 2008 pack on a new iMac. However, I can't update my current Office 2008 12.1.0 version to 12.2.0. I've tried to use the Microsoft AutoUpdate method but it keeps downloading the update again and again but never gets to installing it. I then tried to download the update manually from the MacTopia website and when I tried to install it, I got the error message "the installation has failed". After then, I couldn't launch any of the Office so...

Address Book Access #2
Hello, We used an application here called Syspro. With this application we are trying to automate a few processes and with this in mind, automatic mails would be great. Problem: Can you turn off / change the access notification to E-mail that states 'someone is trying to access your address book / e-mail'. The options give 3 minutes, ten minutes etc. Can we change this? Obviously granting access to mail all the time would not be acceptable. Apologies for not being very clear but the dialog box(es) are not in front of me... Thank you very much, Dam6 ...

Accessing CRM 4 in Outlook Environment over Internet
One of our clients is using Exchange 2007 and many users are accessing Outlook through RPC over HTTP. They are using CRM 4.0. They now want to access CRM in outlook environment over internet. Is that possible without VPN? Has anyone implemented CRM 4 in that kind of environment? Is there any document available? Will appreciate if someone could help. <badri1203@gmail.com> wrote in message news:d79ed213-a088-4aa7-adc5-d55bf2be1f9e@e10g2000prf.googlegroups.com... > Is that possible without VPN? Yes > Is there any document available? The implementation guide covers it, search for ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...