Formatting dates for Access SQL

[Petrol]

Even after reviewing earlier posts and MS Learn on this topic, I'm still struggling to get an SQL INSERT query to behave the way I want it to. Using Access 365 under Windows 11 24H2.
After creating in VBA a new temporary password for a user, I need to insert today's date as an expiry date into a password table. Originally I had forgotten abouty SQL's insistance on American dates, so I wrote

Expiry = Date                          ' Set it to expire immediately
strSQL = "INSERT INTO AccessAuthority (PersonID, AccessLevel, Admin, Hash, Key, Expiry) " _
       & "VALUES (" & PersonID & ", " & AccessLevel & ", " & FrameAdmin & ", '" & Hash & "', 0, #" & Expiry & "#);"
Debug.Print FirstName, Expiry, strSQL
CurrentDb.Execute strSQL               '  Enter the record in the Access Authority table

That obviously failed because the Date function and SQL talk different landuages, so I changed the first line to

 Expiry = Format(Date, "YYYY-MM-DD")      ' Set it to expire immediately

This appears to work - Debug prints Expiry as 8/03/2025, and VarType(Expiry) shows it to be a date value (type 7). But later I need to display a list of people with their expiry dates, and when I format the expiry date in the table as a medium date, it shows it to be 03-Aug-25. How can I get it to stay in the format Date() gave me - the UK/Australian format?

[cactusdata]

You haven't revealed strSQL. Your final expression, if #2025-03-08#, is correct and will insert that date, but variable Expiry must be declared as String.

[Petrol]

Ah, I think you have it. The original code was "Expiry=Dat e" so Expiry was declared a a date; when I changed it to
"Format (Date..." I didn't change the declaration to String. So the strSQL was

INSERT INTO AccessAuthority (PersonID, AccessLevel, Admin, Hash, [Key], Expiry) VALUES (4, 0, 0, 'e52840... (etc) ', 0, #8/03/2025#);

I have now changed the variable to strExpiry, formatted as String, and it works. Thank you muchly!

[NeoPa]

Hi Petrol.

I see you have already found a solution that works for you, but Literal DateTimes and Their Delimiters (#) gives a bit more detail & allows you to make your code fully portable across all areas of the world.

By the way, the format you use is a standard SQL date format that is an alternative to the (possibly more common) USA date format that catches many out - both here & in the USA.

The reason my examples in the linked article use the escape (\) characters for the hyphen is that some European languages use that in their local date formats so it will interpret the string as a local date rather than specifically as Y-M-D (as you intend). Cosmically, these problems are very rare, which is exactly why they appear so rarely on any radars & so few developers handle them correctly. So remember, even the separator characters, as well as the positionning of the D/M/Y sub-parts, can be interpreted according to the locale & catch you out if you aren't careful.

Just as when passing through any user input into your SQL strings, it's always my recommendation that you filter date literals through a function so they're always in the appropriate format.

[NeoPa]

Hi again.

It appears that the links to the old site are not working (Niheel did say he'd hook them all up.), so apologies for that. As a brief resume the format string should be as follows :
#yyyy\-m\-d#
This handles all possible scenarios and locales.

The procedure code I use to ensure all dates are returned in this (string) format is :

'SQLDate takes varDate in Date/Time or YYYYMMDD format and returns it
'formatted for use in SQL.  If blnHash then puts '#'s around it.
'2015-04-26 Updated to support SQL Server format of yyyy-m-d H:m:s.
Public Function SQLDate(ByVal varDate As Variant _
                      , Optional blnHash As Boolean = True) As String
    If IsEmpty(varDate) Or varDate = "" Then Exit Function
    If IsDate(varDate) Then
        varDate = CDate(varDate)
        If TimeValue(varDate) > 0 Then
            SQLDate = Format(varDate, IIf(DateValue(varDate) > 0 _
                                        , "yyyy\-m\-d ", "") & "HH\:nn\:ss")
        Else
            SQLDate = Format(varDate, "yyyy\-m\-d")
        End If
    ElseIf Len(varDate) = 8 Then
        SQLDate = Left(varDate, 4) & "-" & _
                  Val(Mid(varDate, 5, 2)) & "-" & _
                  Val(Right(varDate, 2))
    ElseIf Len(varDate) = 6 Then
        SQLDate = Left(varDate, 4) & "-" & Val(Right(varDate, 2)) & "-1"
    End If
    If blnHash And SQLDate > "" Then SQLDate = "#" & SQLDate & "#"
End Function

[cactusdata]

I have now changed the variable to strExpiry, formatted as String, and it works. Thank you muchly!

Great. You are welcome!

[Petrol]

Sorry for the delayed response ... here in Brisbane for the past week or so we've been battling a big cyclone, with heaps of flooding, power lines down etc, so software development has had to take a back seat.

But thanks for the explanation, NeoPa. That makes it clearer, although I don't think I'll bother with the elegant and thorough option of using the escape character, this time at least. I'm confident my Db will never be used outside Australia (or, indeed, outside Brisbane), and the more complexity I add, the harder it is for anyone else (or me, with my 83-y-o memory) to figure out later what's going on :).

Thanks again, both, for the help.