How to format date (1 Viewer)

[prasadgov]

TIA,

I need to format this date similar to 7/24/2018 8:09:45 AM

The field, field4 value is PREP--20240626072323
I am using Prep_DateTime: IIf([Field2]="98C",Format(Right([Field4],14),"dd/mm/yyyy hh:nn:ss")) but it is incorrect.
What is the format for General date?

 

[plog]

Format() requires the correct input to provide the correct output. You are giving it a string and hoping it can parse it and make it a date for you. It needs help.

MS Access: Format Function (with Dates)

This MSAccess tutorial explains how to use the Access Format function (as it applies to date values) with syntax and examples. The Microsoft Access Format function takes a date expression and returns it as a formatted string.

www.techonthenet.com

DateValue() can convert strings into Dates:

MS Access: DateValue Function

This MSAccess tutorial explains how to use the Access DateValue function with syntax and examples. The Microsoft Access DateValue function converts a string to a date.

www.techonthenet.com

And TimeValue() can convert strings into Time:

MS Access: TimeValue Function

This MSAccess tutorial explains how to use the Access TimeValue function with syntax and examples. The Microsoft Access TimeValue function converts a string to a time.

www.techonthenet.com

You are going to need to extract your field value further and use both of those functions with their own Format() to produce what you want.

 

[gemma-the-husky]

But it is awkward to define a date correctly when there is a time involved.

 

[jdraw]

You may get some insight from this youtube video by Richard Rost.

 

[CJ_London]

I need to format this date similar to 7/24/2018 8:09:45 AM

you need a format month-day-year and AM/PM time format

you tried

"dd/mm/yyyy hh:nn:ss"

which is day-month-year and 24 hour time format

So which is it you actually want?

This code will parse a 14 char string to produce a date format

Function formatDate(s As String) As String
'expects a string in the form yyyymmddhhnnss (14 chars)
Dim i As Integer
Dim d As String
    
    For i = 1 To Len(s)
        d = d & Mid(s, i, 1)
        Select Case i
        
            Case 4, 6 'date separator, insert a -
                 d = d & "-"
                
            Case 8 'date time separator, insert a space
                 d = d & " "
                
            Case 10, 12 'time separator, insert a :
                 d = d & ":"
        
        End Select
                    
    Next i

    'formatDate = Format(d, "m/d/yyyy hh:nn:ss AM/PM")
    'or
    formatDate = Format(d, "dd/mm/yyyy hh:nn:ss")

End Function

in your query it might be called with something like

Prep_DateTime: IIf([Field2]="98C",FormatDate(Right([Field4],14))