Date format error (1 Viewer)

Wysy

Registered User.
Local time
Today, 04:01
Joined
Jul 5, 2015
Messages
292
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

AWF VIP
Local time
Today, 12:01
Joined
Jul 26, 2013
Messages
9,364
Can you post your full SQL code, please?
 

Wysy

Registered User.
Local time
Today, 04:01
Joined
Jul 5, 2015
Messages
292
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:01
Joined
May 7, 2009
Messages
16,775
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

Registered User.
Local time
Today, 04:01
Joined
Jul 5, 2015
Messages
292
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:01
Joined
May 7, 2009
Messages
16,775
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

AWF VIP
Local time
Today, 12:01
Joined
Jul 21, 2014
Messages
1,084
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

Registered User.
Local time
Today, 04:01
Joined
Jul 5, 2015
Messages
292
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!
 

Users who are viewing this thread

Top Bottom