Report SQL Statement Issue

[GLepage]

I have an Access 2000 application that was developed here in the
United States. It has a report that uses an SQL statement as it's
recordsource. The SQL statement contains a WHERE clause that limits
the report to a date range.

strDateRange = "WHERE (([PowerSolve All Issues].[Created]) BETWEEN #"
& dtStart & "# AND #" & dtEnd & "#)"


dtStart & dtEnd are set to date fields on an Access Form where the
user selects the dates.

This report run fine on 50 plus computers it is installed on here at
our U.S. site.

I was requested to share this program with a division we have in
Germany. They are able to open the program and it's associated forms
without issue, but when they run the report they receive an Error
3075. When I looked at the SQL statement using the debug.print command
the Where criteria has the european date format - WHERE (([PowerSolve
All Issues].[Created]) BETWEEN #1.8.2008# AND #31.8.2008#)" - instead
of #8/1/2008# AND #8/31/2008#

I think this is causing the error.

Any ideas how to fix it?

Thanks in advance.

Gene Lepage

 

[Allen Browne]

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how to explicitly format the date as JET expects in
your SQL statements and VBA code.

 

[John Spencer]

Use an unambigous format.

strDateRange = "WHERE [PowerSolve All Issues].[Created] BETWEEN "
& Format(dtStart,"/#yyyy-mm-dd/#") & " AND " & Format(dtEnd,"/#yyyy-mm-dd/#")

You could define a format string and use that
Dim strFormat as string
strFormat = "/#yyyy/-mm/-dd/#"

strDateRange = "WHERE [PowerSolve All Issues].[Created] BETWEEN "
& Format(dtStart,strFormat) & " AND " & Format(dtEnd,strFormat)


For a fuller discussion see International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[GLepage]

See:
    International Date Formats in Access
at:
   http://allenbrowne.com/ser-36.html

The article explains how to explicitly format the date as JET expects in
your SQL statements and VBA code.

Thank you both for the reply.

I will test this solution tomorrow as my contact in Germany has left
for the day.

Gene Lepage

 

[GLepage]

Thank you both for the reply.

I will test this solution tomorrow as my contact in Germany has left
for the day.

Gene Lepage

Sorry for not updating this thread sooner, The solution to format the
date within the SQL statement worked like a charm.

Gene Lepage