VBA convert dates from dd/mm/yyyy to mm/dd/yyyy when opening a rec

[stabilo]

I have a mdb linked to a table (mytab) that contains a field called
date_open in the following format . eg. 01/10/2005, 02/10/2005,... (meaning
1st of october 2005, 2nd of october 2005).

If I run a query on this table with this criterias : date_open =
#01/10/2005# it correcly shows the rows with date open from October 1st.

However, in VBA, if I have the following sql string :
"SELECT * FROM [mytabe] WHERE date_open = " & '"#" & "01/10/2005" & "#"
after I open the recordset with this string, the record set has the rows
matching the dates 10/01/2005 (10th of January).

it looks like the command .openrecordset(strsql) convert the date from
dd/mm/yyyy to mm/dd/yyyy. Any idea how to correct this problem ?

 

[Douglas J Steele]

If the day is less that 13, Access will always interpret nn/nn/nnnn as
mm/dd/yyyy through VBA.

You might find it useful to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I had in my September
2003 "Access Answers" column for Pinnacle Publication's "Smart Access"
newsletter. (The column and accompanying database can be downloaded for free
at http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Tim Ferguson]

If I run a query on this table with this criterias : date_open =
#01/10/2005# it correcly shows the rows with date open from October
1st.

However, in VBA, if I have the following sql string :
"SELECT * FROM [mytabe] WHERE date_open = " & '"#" & "01/10/2005" &
"#" after I open the recordset with this string, the record set has
the rows matching the dates 10/01/2005 (10th of January).

If you look at the SQL generated by the query designer, you will note
that Access reformats dates into jet-compliant (usually USAian
#mm/dd/yyyy#).

When you create your query in VBA and pass it straight to the db engine,
Access does not get to see it, and so you have to do all the reformatting
yourself. Try:

' international ISO format is unambiguous
Const jetFormat As String = "\#yyyy\-mm\-dd\#"

...

jetSQL = "SELECT .... " & _
"WHERE date_open=" & Format(MyDate, jetFormat)


Hope that helps


Tim F

 

[Ed Adamthwaite]

Hi stabilo,
Just pass your date through this function,
sourced from samples published by Ken Getz.
Allen Browne has a similar function (2 brains work alike):

Function SQLDate(varDate As Date) As String
'The Format() function forces the date into American format.
'Format() replaces the slashes with the date separator character
'defined in Control Panel | Regional Settings, so you must specify literal
'slashes in the format string by preceding the slash with backslashes.
'It is necessary that Regional Settings use "dd/mm/yyyy" for the Short Date
style

If IsDate(varDate) Then
SQLDate = Format$(varDate, "mm\/dd\/yyyy")
End If
End Function


I have used this since 02/02/2002 without any problems.
Regards,
Ed.
Ed Adamthwaite