Date format in table

[vjb75]

Hi All,

The following code is supposed to insert a date formatted as dd/mm/yyyy into a table, when i view the mySQL string in the debug window it shows the correct format, however when i open the table it displays the date as mm/dd/yyyy.

The table's date field userdate is formatted as a short date field with an input mask of dd/mm/yyyy.

My computers date settings are set up as dd/mm/yyyy as well

Any ideas on hows to correct this?

Thanks in advance.

Public Function Test()

Dim mySQL As String
Dim dtDate As Date
Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb()

dtDate = Format(Now(), "dd/mm/yy")

mySQL = "INSERT INTO tblCurrentUser( userdate ) values (" & "#" & dtDate & "#" & ");"

Debug.Print mySQL

'*The debugger shows* INSERT INTO tblCurrentUser( userdate ) values (#04/05/2005#);

' However the value in my table tblCurrentUser is05/04/2005 is in mm/dd/yyyy format

Set qdf = db.CreateQueryDef("", mySQL)
qdf.Execute

End Function

 

[Jon K]

The # delimiter takes only dates in US format.

---------------------------------
Dim mySQL As String
Dim dtDate As Date
Dim db As Database


Set db = CurrentDb()

dtDate = Date

mySQL = "INSERT INTO tblCurrentUser( userdate ) values (#" & Format(dtDate, "mm/dd/yyyy") & "#);"

db.Execute mySQL
---------------------------------

Alternatively, you can append a text string to the date field.

mySQL = "INSERT INTO tblCurrentUser( userdate ) values ('" & dtDate & "');"
.

 

[Pat Hartman]

Dates are not stored internally as strings. You are having trouble because you are forcing Access to work with a date formatted as a string. If all you want to do is to add the current date to a table field:

Me.YourDate = Date()
If you also want time then:
Me.YourDate = Now()

The append query you posted only inserts a row with a date, nothing else. What good is that? If you change it to the following, it will append a record with the current date as the userdate value :
mySQL = "INSERT INTO tblCurrentUser( userdate ) values Date());"

 

[vjb75]

RESOLVED - Thanks Pat and Jon.

Thanks for your help both solutions worked well.