Date format problem

[Guest]

Hi All,

I'm Australian, our date format is day month year. My
system is set to dd,mm,yy.

When I try to run a report via a form where start and end
dates are entered ie

DoCmd.OpenReport stDocName, acViewPreview, , "IncidentDate

= #" & txtDateStart & "# AND IncidentDate <= #" &

txtDateEnd & "#"

the dates from the form are perceived to be month day year.
The fields on the form are set to short date eg 19-06-03.
The report shows short date 19-06-03 however when the
dates 01-03-03 and 01-12-03 are entered in the form the
report delivers dates from 3 Jan 03 to 12 Jan 03.

Can this be fixed or will I have to live with it.
Thanks for your assistance
Gail

 

[Arvin Meyer [MVP]]

VBA wants the US date format, but here's some code that will help:

http://www.mvps.org/access/datetime/date0005.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Van T. Dinh]

Date literals , i.e. those enclosed by hashes, *must* be in US format
"mm/dd/yyyy" regardless of your regional settings for JET to process
accurately.

Try:

DoCmd.OpenReport stDocName, acViewPreview, , _
"[IncidentDate] BETWEEN " & Format(txtDateStart, "\#mm/dd/yyyy\#") & _
" AND " & Format(txtDateEnd, "\#mm/dd/yyyy\#")

 

[Steve Schapel]

Gail,

In addition to the suggestions from Arvin and Van, I generally find it
easier to use the CLng() function to handle this, e.g....
"IncidentDate Between " & CLng(txtDateStart) & " And " & CLng(txtDateEnd)