Date format error (1 Viewer)

[Wysy]

Hi,
I have just run into the classic date format problem and do find the solution that i used to have found.
Entering data with sql into table.
When default date field is =Date(), the format of the date is correct as it is set by w10 (UK)
When i choose the date from the date picker the format is correct only if the day number is higher as the month. If it smaller the day and the month mixed up.
I have tried to fix in vba by adding Format([DOV], "\#mm\/dd\/yyyy\#") wher DOV is the name of field.
How do i solve it, what do i do wrong?
thanks

 

[Minty]

Can you post your full SQL code, please?

 

[Wysy]

z = Forms!fsubMain1.Form.txtID
d = Format([DOV], "\#dd\/mm\/yyyy#\")
xx="test"
For i = 0 To rs.RecordCount - 1
    xx = arrayx(0, i)
    strX = "INSERT INTO tbDiagServMat(TM_Date,HorseID,DiagServiceMaterial) " & _
         "VALUES (#" & d & "#,'" & z & "','" & xx & "');"
    DoCmd.SetWarnings False
        DoCmd.RunSQL strX
    DoCmd.SetWarnings True
    

Next i

 

[arnelgp]

what is arrayx(0, i)?

you already Formatted d, so no need to format it again:

strX = "INSERT INTO tbDiagServMat(TM_Date,HorseID,DiagServiceMaterial) " & _
"VALUES (" & d & ",'" & z & "','" & xx & "');"

 

[Wysy]

arrayx is the array to take entries for the sql.
yes d is formated but it mixes day and month if day is higher then 12

 

[arnelgp]

mixes day and month if day is higher then 12

you need to Change the format to American date first:

d = Format([DOV], "\#mm\/dd\/yyyy\#")

 

[cheekybuddha]

d = Format([DOV], "\#dd\/mm\/yyyy#\")

This is wrong. Arnel is correct.

To explain, when passing dates to SQL you must do so in an unambiguous way. The Jet/ACE query engine has no knowledge of your regional settings, so expects dates to be either mm/dd/yyyy or yyyy-mm-dd format. It does not matter that you wish to see dates in UK format.

Once in the table, you will see the dates displayed as you are used to.

 

[Wysy]

thanks for the inputs. Unfortunately the table has had it incorrect too. However i ve gotten it working.
I have deleted the date delimiter in the value section of the sql statement. It is correct now. I will keep testing and report back. Thanks a lot!

 

[cheekybuddha]

I have deleted the date delimiter in the value section of the sql statement

Yes, you already added the date delimiters in your Format() when you assigned d