ti********@cciw.ca wrote in news:1116446319.453342.180740
@g44g2000cwa.googlegroups.com:
I'm running into a problem using dates in a query which supplied data
to a report. The WHERE clause of the query is dynamically generated,
based on selections made on a form. Users enter dates in an unbound
text box, with the format set to 'dd/mm/yyyy'.
In the SQL code generated in the report_open event, I set the dates in
the WHERE clause to:
strDates = Forms![frmreports].cboDateField.Column(1) & " BETWEEN
CONVERT(DATETIME, '" & Forms![frmreports].txtStartDate.Value & "', 103)
AND CONVERT(DATETIME, '" & Forms![frmreports].txtStopDate.Value & "',
103)"
with the CONVERT function, and assumed this would elimiate the problems
with Regional Settings on client machines. However, I am getting a
DateConversion error when the report is opened with the dates
31/03/2005 and 18/05/2005 (start and end)
Can anyone give me a suggestion?
TIA
Tim Pascoe
Are you sure Forms![frmreports].txtStartDate.Value is returning the string
"31/03/2005"? Could it be returning something else? Have you tried
(air script)
Format(Forms![frmreports].txtStartDate.Value,"dd\/mm\/yyyy")?
If this doesn't help perhaps you could Debug.Print the string and be
completely sure what you are sending.
As an aside, and I am not suggesting you do this, I always format the date
as "YYYY-MM-DD" and cast it as datetime or smalldatetime (for those
unfamiliar with T-SQL, smalldatetime is what one gets after one eats
garlic). I have never had any problem with this.
--
Lyle
To subject an enemy belligerent to an unfair trial, to charge him with an
unrecognized crime, or to vent on him our retributive emotions only
antagonizes the enemy nation and hinders the reconciliation necessary to a
peaceful world.
Justice Frank Murphy
Yamashita v. Styer, 327 U.S. 1 (1946)