Weird Date Behaviour

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

Guest

I am using the below code to generate an SQL statement. It works OK but there
is some kind of weird behaviour going on with the dates.

I am using Active X Calenders to generate the start and end dates and I have
added 2 debug statements to check the dates. In the Debug window it is
printing the correct uk dates ie: 01/04/2004 and the SQL statement in the
debug window also displays the correct date but when this value is parsed to
the query it changes to 04/01/2004! If I select a date like 30/09/2004 it
will the parse the correct date to the query but when the date could be
either UK or USA format it chooses USA format.

Has anyone ever experienced this before?

If subfrmDateRange!ActiveXCtl4.Value = Date Then
MsgBox ("You cannot run report for today only?")
Exit Sub
Else
If subfrmDateRange!ActiveXCtl4.Value > subfrmDateRange!ActiveXCtl1.Value
Then
MsgBox ("The Start Date must be before the end Date")
Exit Sub
Else
dtStartDate = subfrmDateRange!ActiveXCtl4.Value
dtEndDate = subfrmDateRange!ActiveXCtl1.Value
MsgBox ("Date Range " & dtStartDate & "-" & dtEndDate)
End If
End If

Debug.Print dtStartDate
Debug.Print dtEndDate

strSQL = "SELECT qryAllinfo.SuppCode, tblSuppliers.SupplierName,
qryAllinfo.GLCode, qryAllinfo.AccountDescription, " & _
"qryAllinfo.Date, qryAllinfo.Amount, qryAllinfo.Industry " & _
"FROM tblSuppliers INNER JOIN qryAllinfo ON
tblSuppliers.SupplierCode = qryAllinfo.SuppCode " & _
"WHERE qryAllinfo.Date Between #" & dtStartDate & "# AND #" &
dtEndDate & "# AND qryAllinfo.Industry " & strCommodity & ";"
 
Date behaviour in Access can sometimes be a bit of a pain. Depending on
language settings en how the field is defined in your database.

When using dates I ALWAYS use the format function to format the tekst/date
in a consequent manner.

format(<text string>,"yyyy-mm-dd") or however you want to store / display it.

Greetz
 
Edgar Thoemmes said:
I am using the below code to generate an SQL statement. It works OK
but there is some kind of weird behaviour going on with the dates.

I am using Active X Calenders to generate the start and end dates and
I have added 2 debug statements to check the dates. In the Debug
window it is printing the correct uk dates ie: 01/04/2004 and the SQL
statement in the debug window also displays the correct date but when
this value is parsed to the query it changes to 04/01/2004! If I
select a date like 30/09/2004 it will the parse the correct date to
the query but when the date could be either UK or USA format it
chooses USA format.

Has anyone ever experienced this before?

If subfrmDateRange!ActiveXCtl4.Value = Date Then
MsgBox ("You cannot run report for today only?")
Exit Sub
Else
If subfrmDateRange!ActiveXCtl4.Value >
subfrmDateRange!ActiveXCtl1.Value Then
MsgBox ("The Start Date must be before the end Date")
Exit Sub
Else
dtStartDate = subfrmDateRange!ActiveXCtl4.Value
dtEndDate = subfrmDateRange!ActiveXCtl1.Value
MsgBox ("Date Range " & dtStartDate & "-" & dtEndDate)
End If
End If

Debug.Print dtStartDate
Debug.Print dtEndDate

strSQL = "SELECT qryAllinfo.SuppCode, tblSuppliers.SupplierName,
qryAllinfo.GLCode, qryAllinfo.AccountDescription, " & _
"qryAllinfo.Date, qryAllinfo.Amount, qryAllinfo.Industry " &
_ "FROM tblSuppliers INNER JOIN qryAllinfo ON
tblSuppliers.SupplierCode = qryAllinfo.SuppCode " & _
"WHERE qryAllinfo.Date Between #" & dtStartDate & "# AND #" &
dtEndDate & "# AND qryAllinfo.Industry " & strCommodity & ";"
Click to expand...

Whenever a date literal is ambiguous, Jet SQL will interpret it as being
in US standard date format: month/day/year. You should use the Format
function in building your SQL string to format your dates into either
the US date format or an unambiguous format.

I usually do it like this:

' ...
"WHERE qryAllinfo.Date Between " & _
Format(dtStartDate, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(dtEndDate, "\#mm/dd/yyyy\#") & _
' ...
 
If I select a date like 30/09/2004 it
will the parse the correct date to the query but when the date could be
either UK or USA format it chooses USA format.

Has anyone ever experienced this before?
Click to expand...

This is expected behaviour and documented in the help files: look up Date
Literal Values. In order to work correctly in an international context, Jet
interprets dates in a small number of formats, specifically USA and ISO. To
me, the fact that #25/12/2003# fails to raise an error is a Serious Bug but
let's pass on.

The User Interface looks after this for you, so when you enter 25/12/2000
in the query grid, Access translates this into #12/25/2000# in the
underlying SQL command.

When you construct your own SQL commands, though, you are on your own, and
you just have to pass them in the correct format. For vague reasons, I
prefer the international ISO format, so my code is littered with things
like

public function SQLDate(SomeDate as Date) as String

' the backslashes are necessary to prevent other
' country setups hijacking the symbols
Const c_strISO8601 = "\#yyyy\-mm\-dd\#"

' do the translation
SQLDate = Format(SomeDate, c_strISO8601)

end function


SQL = "... AND ReferDt = " & SQLDate(txtInputDate) & "..."

Hope that helps


Tim F
 
Back
Top