Solved Problem with dates in sql

[disgracept]

Hi all.

I'm having a problem that is getting me nuts...
I have a form to make updates to my tables that has a tab control with several pages. Each page is for a specific update.

Then i made this sub to update the corresponding fields of the tables:

Private Sub updateImovel(myTable As String, myPage As String, maxCtrl As Integer)
    Dim mySQL As String
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim currChk As Control
    Dim currCtrl As Control
    Dim currIndx As Integer
    Dim firstUpd As Boolean
   
    firstUpd = True
   
    mySQL = "UPDATE " & myTable & " SET "
   
    For currIndx = 1 To maxCtrl
        Set currChk = Me.Controls("chk_" & myPage & "_" & currIndx)
        If currChk Then
            Set currCtrl = Me.Controls(currChk.Tag)
            If firstUpd Then
                firstUpd = False
            Else
                mySQL = mySQL & ", "
            End If
            Dim textCtrl As String
            If Nz(currCtrl, "") = "" Then
                textCtrl = Chr(34) & Chr(34)
            Else
                If IsDate(currCtrl) Then
                    textCtrl = "#" & currCtrl & "#"
                Else
                    textCtrl = currCtrl
                End If
            End If
            mySQL = mySQL & myTable & "." & currCtrl.Tag & "=" & textCtrl
        End If
    Next
   
    mySQL = mySQL & " WHERE (((" & myTable & ".Imovel_ID)=" & txt_Imovel_ID & "));"
   
    MsgBox (mySQL)
    DoCmd.SetWarnings False
    'DoCmd.RunSQL mySQL
    DoCmd.SetWarnings True

    Set qdf = db.QueryDefs("Query1")
    qdf.SQL = mySQL

    Set qdf = Nothing
    Set db = Nothing
End Sub

This is supposed to run the sql in the commented line, but for now i just set the querydef of Query1 to show whats happening.

When a textbox has a date and the day number is less then the month number (for example 04/12/2023) the result query swaps the day with the month and i get 12/04/2023 as you can see in the image below...

But the mySQL variable holds the correct value:

And the SQL of the query also has the correct value:

But it inserts in the table the incorrect one...
And i can't figure why this happens... All the date formats are correct and set to "Short date" with the format of my system - dd/mm/yyyy.
What is happening and how can i solve this?
Thanks in advance for the help.

 

[Gasman]

format of my system - dd/mm/yyyy.

There is your problem.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[disgracept]

Hi @Gasman, thanks for the answer.

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

So when i construct my SQL i need to put the dates in the mm/dd/yyyy format???

But my textbox is in dd/mm/yyyy so i'll need to reconstruct the date to the correct form??

 

[CJ_London]

Just use the format function to convert from dd/mm/yyyy to mm/dd/yyyy (plus the # delimiters) although I prefer the sql standard of yyyy-mm-dd

 

[Gasman]

I used this is my DBs, found on the net.

Option Compare Database
Option Explicit

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

        Me.cboDates = DLookup("StepsID", "tblDates", "StepsDate = " & Format(Date, strcJetDate))

As you use ddmmyyyy as do I, then we have to convert them any time for SQL or functions like DLookup criteria.

If you use form controls directly, that is not required, as that is interpreted as just the date number.

 

[disgracept]

Thanks guys for all your help.

Didn't know that SQL always expects USA format, regardless of the machine definitions.

I changed the line to textCtrl = "#" & Format(currCtrl, "mm/dd/yyyy") & "#" and it worked.

Thanks again.
Cheers

 

[Gasman]

Not sure about all SQL, but Access SQL certainly does.
As you found out if the date is 13/07/2024 it knows it is 07/13/2024 as there are not 13 months in the year, but if you have 06/07/2024, that can be ambiguous, so always either use what you have just done, or as you can see from my snippet, I changed to yyyy-mm-dd from my initial mm/dd/yyyy format.

 

[disgracept]

Thanks @Gasman