MS Access SQL "INSERT INTO" statement produces date in wrong format despite correct region-CodePudding

My computer's regional date setting is dd/mm/yyyy. I am using MS Access. I would like to insert records into a database using the SQL INSERT INTO statement. When I try to insert a date using the #dd/mm/yyyy# syntax, and view the resulting record in the table after, it turns out the record displays the date in the format mm/dd/yyyy instead, but ONLY for the first 10 days of the month; if the day is 11 onwards, the record displays dd/mm/yyyy as intended.

For example if in SQL code I input #09/02/2022#, the table will display the record with the date 02/09/2022 instead. However if my SQL code is#11/02/2022#, then the correct order 11/02/2022 is shown in the record.

Please help.

CodePudding user response：

Ok, the way this works?

You don't have to care, know, or think about the users regional format settings.

So, if you drop some control on a form? Just make sure that control is set to a date type format. Your done.

BUT ONE big whopper:

IN ANY AND ALL cases, your string based date format MUST be in USA format. Or you can use ISO date format.

 dim MyDate   as Date

 MyDate = me.InvoiceDate

So, now we have a internal format date variable. How to insert into a table?

dim strSQL  as string

strSQL = "INSERT INTO tblInvoice (InvoiceNum, InvoiceDate, InvoiceAmount " & _
         "VALUES (1234, " & quDate(MyDate) & ",50)"

So, you ALWAYS format the date value into USA format.

You can type that format command over and over, but that fast becomes tiring.

so, I use a little helper function:

Public Function quDate(dt As Date) As String

   quDate = "#" & Format(dt, "mm\/dd\/yyyy") & "#"

End Function

Public Function quDateT(dt As Date) As String
  
     ' return formatted date  with time
     
     quDateT = "#" & Format(dt, "mm\/dd\/yyyy HH:NN:SS") & "#"    
     
  End Function

So, you don't have to care about the date and regional format, but for a in-line SQL insert command that you build in code? Yes, you MUST convert to USA format of mm/dd/yyyy.

So, you can display dates in any format. For forms, for reports - not a problem.

However, the ONLY exception here is your code that builds a insert statement. That date string format must be #mm/dd/yyyy#.

Or, ISO:

 #yyyy-mm-dd#

So, either format is fine, but it is a hard and fast rule that you must conform to.

So, from a text box on a form, if not data bound, then you want to ensure that the text box is set as a date type text box (fomrat date).

then in code:

dim strSQL as string

strSQL = "INSERT INTO tblFun (BirthDate) " & _
        "VALUES (#" & format(txtDate,"mm/dd/yyyy") & "#)"

currentdb.Execute strSQL

Or, if you have that helper function, then this:

strSQL = "INSERT INTO tblFun (BirthDate) " & _
        "VALUES (" & qudate(txtDate) & ")"