How to convert a string from Json to Ms Access date & time

[nector]

Hello there!

Can someone help me to convert this date/time string to normal date:

String : 20230626121231

I want a function to convert the above string to look like this : 2023/06/26 12:12:31

Kindly try it in Ms Access before posting , I tried this function below it failed:

Format(20230626121231, "mm/dd/yyyy hh:hh:ss am/pm")

 

[theDBguy]

Did you try enclosing the string with double quotes?

 

[nector]

Actual that is coming from a control called invoicedate

 

[Josef P.]

DateValue = CDate(Format("20230626121231", "@@@@-@@-@@ @@:@@:@@"))
xyz = Format(DateValue, "mm\/dd\/yyyy hh:nn:ss am/pm")

 

[nector]

Josef P.

The code is giving an error called overflow runtime 6:

Me.txtEsd = Format(DLookup("EsDTime", "tblEfdReceipts", "INVID =" & [InvoiceID]), "mm/dd/yyyy hh:hh:ss am/pm")

 

[jdraw]

nector,

This format is incorrect "mm/dd/yyyy hh:hh:ss am/pm"
should be nn for minutes

 

[Josef P.]

202306261231 is not a number corresponding to a date value. Therefore you can't convert it with "mm/dd/yyyy ...".

Have you tried the code I gave you?

 

[nector]

nector,

This format is incorrect "mm/dd/yyyy hh:hh:ss am/pm"
should be nn for minutes

nector,

This format is incorrect "mm/dd/yyyy hh:hh:ss am/pm"
should be nn for minutes

Me.txtEsd = Format(DLookup("EsDTime", "tblEfdReceipts", "INVID =" & [InvoiceID]), "mm/dd/yyyy hh:nn:ss am/pm")

Im still getting the same message

 

[jdraw]

See Josef's answer in #7