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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ?
 
stabilo said:
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 ?
Click to expand...

Date literals in Access queries MUST be either...

USA format mm/dd/yyyy or (ugh) mm/dd/yy
ISO format yyyy-mm-dd
A format with the month in alpha Mar 5, 2005 or 20 February 2005

This is how it has always been.
 
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).
Click to expand...

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
 
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
 
Back
Top