Results 1 to 15 of 15
  1. #1
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8

    Formatting a text string as a time (military) without using Replace

    Ok, this is a long one so bear with me. I have a database where the time (for whatever reason) is formatted as a text string. I need to run a query that will show that text string as a time in military format. One way I tried to do this was in a nested IIF statement by analyzing the first 2 left characters and using the Replace function. This worked for the query, but when I went to import data from that query to create a pivot table, that query did not show up. When I deleted the nested IIF statement that used the Replace function, the query showed up. I guess it doesn't like the Replace function for some reason. Here's the nested IIF I was using to replace times of 1:00-8:00 with 13:00-20:00:



    wave:IIf(Left([info],2)="1:",Replace([info],"1:","13:"),IIf(Left([info],1)="2",Replace([info],"2:","14:"),IIf(Left([info],1)="3",Replace([info],"3:","15:"),IIf(Left([info],1)="4",Replace([info],"4:","16:"),IIf(Left([info],1)="5",Replace([info],"5:","17:"),IIf(Left([info],1)="6",Replace([info],"6:","18:"),IIf(Left([info],1)="7",Replace([info],"7:","19:"),IIf(Left([info],1)="8",Replace([info],"8:","20:"),[info]))))))))

    Again, this works in the query, but the query does not show up as a valid choice when I try to use it for a pivot table. I have been messing around with Format and CDate functions, but no luck so far. If I convert it to show AM/PM it shows the times as 01:00 AM when it should be 01:00 PM.

    Really stuck on this, hope someone on here has some insight. Thanks for your help!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please give us a few samples of "Info" and what you want returned/displayed.

  3. #3
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8
    Currently the info field displays as 10:00, 1:30, 9:30, etc. all text. I want them to display as 10:00, 13:30, 9:30, etc. Basically, if the time starts with 1-8, I want it converted to military time (13:00-20:00).

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try Format(YourTextFieldHere, "hh:nn")

    I tested with
    Code:
    Sub MilTime()
    Dim t1 As String
    Dim t2 As String
    Dim t3 As String
    t1 = "23/3/2013 4:59 AM"
    t2 = "9/9/2013 4:22 PM"
    t3 = "1/11/2013 10:15 PM"
    Debug.Print t1 & "........" & Format(t1, "hh:nn")
    Debug.Print t2 & "........" & Format(t2, "hh:nn")
    Debug.Print t3 & "........" & Format(t3, "hh:nn")
    End Sub
    and got these results

    Code:
    23/3/2013 4:59 AM........04:59
    9/9/2013 4:22 PM........16:22
    1/11/2013 10:15 PM........22:15

  5. #5
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8
    All that does is change 2:00 to 02:00, tried that before.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Really,
    did you notice it changed
    t1 = "23/3/2013 4:59 AM" to ........04:59

    t2 = "9/9/2013 4:22 PM" to ........16:22

    t3 = "1/11/2013 10:15 PM" to .......22:15

  7. #7
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8
    That's part of the problem, the times are entered as 2:00 for 2PM, there is no AM/PM designation in the input. It's just a text field. That's why that won't work, because it thinks 2:00 is 02:00am (which it is, but I am shooting for 14:00)

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I asked for sample data before -- can you supply some real data.

    Now, if you do not know, or can not calculate whether 2 means 2 or 14, you may not have enough info to solve the issue.
    But you're going to have to show what you have -real data - if you're going to get some assistance from readers.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It sounds like the point is that 2 will always be pm. How about this, using the field name instead of the literal time inside the CDate() function:

    ?iif(cdate("2:00")<#9:00#,cdate("2:00") + #12:00#,cdate("2:00"))
    2:00:00 PM
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8
    Here's what the source data looks like in the info column

    info
    10:00
    10:30
    11:30
    12:00
    12:30
    1:30
    2:30
    3:30
    9:30
    10:30
    11:30
    12:00
    12:30
    1:30
    2:00
    2:30
    3:30
    9:30
    10:00
    4:00
    5:30
    6:00
    7:30
    4:00
    5:00
    5:30
    6:00
    6:30
    7:00
    2:00
    OPEN
    11:00
    3:00
    1:00
    9:00
    11:00
    1:00
    11:00
    1:00
    3:00
    9:00
    12:00
    2:30
    9:30
    5:00
    700
    3:00
    11:00
    1:00
    3:00
    9:00
    11:30
    2:00
    9:00

    The problem is I need it to display in military time, and as you can see there are possibilities for times mid-hour. I can create a nested IIF statement that will change it if it's on the hours of 1:00-8:00, but I cannot add code to include mid-hours or I get an error that it's too complex. Tried the same thing using the Switch function, but again it is too complex.

  11. #11
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8
    Yes, times of 1:00-8:00 will always be 13:00-20:00. Trying your CDate function suggestion...

  12. #12
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8
    That CDate function returned a time of 2:00:00 PM for every line...

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you do as Paul suggested?

    How about this, using the field name instead of the literal time inside the CDate() function:
    Use your field name instead of the 2:00 in his expression.

  14. #14
    sedonapinesit is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    8
    That appears to be working, thanks a lot! Is there an easy way I can trim it down to just hours and minutes and drop the seconds?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    IIf(CDate("2:00")<#9:00#,Format(CDate("2:00") + #12:00#,"HHNN"),Format(CDate("2:00"),"HHNN"))

    or maybe

    Format(IIf(CDate("2:00")<#9:00#, CDate("2:00") + #12:00#, CDate("2:00")), "HHNN")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Military Time Blues
    By viperbyte in forum Forms
    Replies: 19
    Last Post: 04-14-2020, 06:45 AM
  2. Military Time
    By tshirttom in forum Access
    Replies: 3
    Last Post: 07-29-2011, 01:29 PM
  3. Issue Using Replace in SQL String
    By starryNight in forum Programming
    Replies: 15
    Last Post: 06-03-2011, 01:24 PM
  4. Replace a string/text in ms-access db
    By anziga in forum Queries
    Replies: 4
    Last Post: 12-31-2010, 06:40 PM
  5. Text string formatting
    By DanW in forum Forms
    Replies: 2
    Last Post: 11-02-2009, 11:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums