Results 1 to 13 of 13
  1. #1
    Lisa Roby is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2020
    Posts
    6

    Question Timestamp converted to just date

    I am trying to convert a timestamp (20200212091009) to the date 2-12-2020 in a query and I keep getting an error.



    The following is the expression I have tried:

    Finalize_Date: DateSerial
    (Left([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],4),
    Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],5,2),
    Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],7,2))

    and

    CDate
    (Left([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],4
    & "/" &
    Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],5,2)
    & "/" &
    Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],7,2))) AS Finalize_Date,

    I am at a loss at this point, any help would be appreciated.

    TIA

    Lisa

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    Where does this timestamp originate?

  3. #3
    Lisa Roby is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2020
    Posts
    6
    In the table R09CABPRD_CAR_MOVE and is system generated.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    Hi Lisa,

    Can you try:
    Finalize_Date: DateSerial(CInt(Left([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],4)),CInt(Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],5,2)),CInt(Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],7,2)))

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Lisa Roby is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2020
    Posts
    6
    Still getting #ERROR

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    your CDate method is missing a bracket on the left line and has an extra one on the last line. It works if corrected


    Dateserial also works for me

    ?DateSerial(Left(20200212091009,4),Mid(20200212091 009,5,2),Mid(20200212091009,7,2))
    12/02/2020 (UK date format)

    it is more common to use a dot (.) rather than a bang (!) to separate table and field names. Unless your field name is repeated in another table in the same query, you can remove [R09CABPRD_CAR_MOVE]!

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    You could just use a function.
    Code:
    Function az(timestamp As String) As Date
    Dim DS As String
    DS = Left(timestamp, 8)
    az = DateSerial(Left(DS, 4), Mid(DS, 5, 2), Right(DS, 2))
    End Function

  8. #8
    Lisa Roby is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2020
    Posts
    6
    Thank You all for your input and I am baffled as to why they are not working, the CDate came close I got // with no date. The DateSerial I am still getting #Error. I did not put the bang(!) in the formula, Access did, changing it made no difference. As for the function, I am still relatively new to this and I do not understand it. Thank You Lisa

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    see this db based on your one example provided

    it works for both cdate and dateserial
    Attached Files Attached Files

  10. #10
    Lisa Roby is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2020
    Posts
    6
    I think my issue is the fact that it is in a query and that field is blank, once it populates another query and the table there is data. So I am guessing it needs to return a zero until there is data to convert. Is this possible? lisa

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    You mean like this :

    FinalizeDate:IIF(IsNull([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1]),Null,CDate(Left([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],4) & "/" & Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],5,2) & "/" & Mid([R09CABPRD_CAR_MOVE]![VC_VAR_FLD1],7,2)))

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Lisa Roby is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2020
    Posts
    6
    No longer getting the #Error, but it is not converting now!! SMH

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    I think at this point a small db sample would be the best way to help us see the problem. Just a few representative records to replicate the issue (remove any sensitive info) along with the query where you try this.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 8
    Last Post: 11-16-2017, 03:07 PM
  2. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  3. Replies: 2
    Last Post: 05-29-2015, 11:16 AM
  4. Replies: 3
    Last Post: 11-12-2014, 05:31 PM
  5. Replies: 1
    Last Post: 09-03-2014, 03:23 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
  •  
Tech Forums: Microsoft Office Forums