Results 1 to 11 of 11
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Reformatting a given date string


    If you run the code below, you'll see that the task is to take one kind of date expression and convert it to another. Every time I find myself writing any kind of code of this nature I have to ask "there has to be a simpler way!". If there is I've failed so far to find it. Any hints?
    Code:
    Option Compare Database
    Option Explicit
    Dim Temp() As String
    Dim strTime As String
    Dim strDay As String
    Dim strdate As String
    
    Private Sub TestRun()
    MsgBox ReFmt("Tue, 02 Jul 2019 02:18:46 +0000")
    End Sub
    
    Private Function ReFmt(TWCreDate As String) As String
    Temp = Split(TestDate, " ")
    strTime = Temp(4) & "Z"
    
    Temp = Split(TestDate, ",")
    strDay = Left(Temp(1), 2)
    
    Temp = Split(Temp(1), ":")
    strdate = Left(Temp(0), Len(Temp(0)) - 3)
    
    ReFmt = Format(strdate, "yyyy-mm-ddT") & strTime
    End Function

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Consistency of structure is critical in string manipulation. If the original string data always has 3-character day name followed by comma+space and always a "+0000" at end (what is that?), here is expression that can be used in query, textbox, or VBA.

    CDate(Mid(Left(x, InStr(x, "+")-2), 6))

    or

    Format(Mid(Left(x, InStr(x, "+")-2), 6), "yyyymmddT")

    However, both will fail if x is null.


    How can your function work? Variable TestDate is not declared and set.
    Last edited by June7; 07-03-2019 at 12:14 AM.
    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.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    "+0000" at end (what is that?)
    I have no idea what the "+0000" is all about. I've never seen an expression like that trailing a time. Anyway, I'm off for a couple of days so I'll pick up with your suggestion on Friday.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you 'll find that the "+0000" is a UTC offset - https://en.wikipedia.org/wiki/UTC_offset it is a standard way of representing different time zones.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you could just use a single line

    format(left(split("Tue, 02 Jul 2019 02:18:46 +0000",",")(1),21),"yyyy-mm-ddT hh:mm:ssZ")

    which gives you

    2019-07-02T 02:18:46Z

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Of course, it never dawned on me that the source of the date expression
    "Tue, 02 Jul 2019 02:18:46 +0000"
    is an international service and would necessarily need qualify the time zone.

    Thanks,
    Bill

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Ha Ha, you beat me to it Your statement reminds me of the early days of APL (Arithmetic Programming Language) where mathematicians would challenge one another to transform complex matrices with a single line of code. I think one can rely on the "21" string length.

    My APL type approach that I dreamed up at 3AM that doesn't rely on the length assumption:
    Code:
    Sub testit()
    MsgBox Format(Split(Split("Tue, 02 Jul 2019 02:18:46 +0000", "+"), ","), "yyyy-mm-ddT hh:mm:ssZ")
    End Sub
    Was met with a "Type Mismatch" so I'll have to "Play" with that on Friday.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    ok, if you don't want to rely on the 21 chars

    format(split(replace("Tue, 02 Jul 2019 02:18:46 +0000","+",","),",")(1),"yyyy-mm-ddT hh:mm:ssZ")

    or doing the double splits

    Format(Split(Split("Tue, 02 Jul 2019 02:18:46 +0000", "+")(0), ",")(1), "yyyy-mm-ddT hh:mm:ssZ")

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    The double splits failed, as posted in #7.
    Was met with a "Type Mismatch" so I'll have to "Play" with that on Friday.
    The probability of encountering a time zone other than "+0000" is pretty slim, so your "Replace" solution is likely 99% safe.

    Thanks,
    Bill

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The double splits failed, as posted in #7.
    but not as I posted in #8

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    It would help if shared the index value with Access

    The "double Split" works fine when it's coded correctly.

    Thanks,
    Bill

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

Similar Threads

  1. Replies: 5
    Last Post: 02-20-2018, 07:25 PM
  2. Replies: 11
    Last Post: 06-01-2015, 08:39 PM
  3. Replies: 7
    Last Post: 12-26-2014, 12:17 PM
  4. Replies: 5
    Last Post: 01-23-2013, 11:42 PM
  5. Reformatting RTF to Text
    By le_carrington in forum Import/Export Data
    Replies: 3
    Last Post: 11-26-2010, 04:22 PM

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