I am investigating an SQL issue that exists in Access 2007 that doesn't exist
in Access 2003. I did not write the SQL code. I am assisting in resolving
the issue. Both versions of Access are running on a Citrix server.

Here is the problem:

In Access 2007 SQL, the following statement:
AND NESWHSLDT BETWEEN '02/14/2010' AND '02/20/2010' will convert to the
following when the date is changed to another date. For example when the 14
in 02/14/2010 is changed to 15, this is what the SQL changes to:
AND NESWHSLDT BETWEEN '02/015/2010' AND '02/20/2010'. An extra zero is
inserted into the SQL.

This does not occur with the same SQL running in Access 2003. We have
verified that all date formats are the same. Any help would be appreciated.
--
Thanks

Use # as the delimiter around the literal date values in your SQL statement,
not the single-quote character.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"MTate" <(E-Mail Removed)> wrote in message
news:4CAB79DB-C0EA-44FA-BAAA-(E-Mail Removed)...
> I am investigating an SQL issue that exists in Access 2007 that doesn't
> exist
> in Access 2003. I did not write the SQL code. I am assisting in
> resolving
> the issue. Both versions of Access are running on a Citrix server.
>
> Here is the problem:
>
> In Access 2007 SQL, the following statement:
> AND NESWHSLDT BETWEEN '02/14/2010' AND '02/20/2010' will convert to the
> following when the date is changed to another date. For example when the
> 14
> in 02/14/2010 is changed to 15, this is what the SQL changes to:
> AND NESWHSLDT BETWEEN '02/015/2010' AND '02/20/2010'. An extra zero is
> inserted into the SQL.
>
> This does not occur with the same SQL running in Access 2003. We have
> verified that all date formats are the same. Any help would be
> appreciated.
> --
> Thanks

Thank You
--
Thanks


"Allen Browne" wrote:

> Use # as the delimiter around the literal date values in your SQL statement,
> not the single-quote character.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "MTate" <(E-Mail Removed)> wrote in message
> news:4CAB79DB-C0EA-44FA-BAAA-(E-Mail Removed)...
> > I am investigating an SQL issue that exists in Access 2007 that doesn't
> > exist
> > in Access 2003. I did not write the SQL code. I am assisting in
> > resolving
> > the issue. Both versions of Access are running on a Citrix server.
> >
> > Here is the problem:
> >
> > In Access 2007 SQL, the following statement:
> > AND NESWHSLDT BETWEEN '02/14/2010' AND '02/20/2010' will convert to the
> > following when the date is changed to another date. For example when the
> > 14
> > in 02/14/2010 is changed to 15, this is what the SQL changes to:
> > AND NESWHSLDT BETWEEN '02/015/2010' AND '02/20/2010'. An extra zero is
> > inserted into the SQL.
> >
> > This does not occur with the same SQL running in Access 2003. We have
> > verified that all date formats are the same. Any help would be
> > appreciated.
> > --
> > Thanks
>
> .
>

This was tested today and it did not work. For some reason in the SQL
(Access 2007) once the statement is saved it inserts a ' in the SQL. So now
it reads BETWEEN ' #02/14/2010# and #02/14/2010#

Any ideas? This does not occur in Access 2003.
--
Thanks


"Allen Browne" wrote:

> Use # as the delimiter around the literal date values in your SQL statement,
> not the single-quote character.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "MTate" <(E-Mail Removed)> wrote in message
> news:4CAB79DB-C0EA-44FA-BAAA-(E-Mail Removed)...
> > I am investigating an SQL issue that exists in Access 2007 that doesn't
> > exist
> > in Access 2003. I did not write the SQL code. I am assisting in
> > resolving
> > the issue. Both versions of Access are running on a Citrix server.
> >
> > Here is the problem:
> >
> > In Access 2007 SQL, the following statement:
> > AND NESWHSLDT BETWEEN '02/14/2010' AND '02/20/2010' will convert to the
> > following when the date is changed to another date. For example when the
> > 14
> > in 02/14/2010 is changed to 15, this is what the SQL changes to:
> > AND NESWHSLDT BETWEEN '02/015/2010' AND '02/20/2010'. An extra zero is
> > inserted into the SQL.
> >
> > This does not occur with the same SQL running in Access 2003. We have
> > verified that all date formats are the same. Any help would be
> > appreciated.
> > --
> > Thanks
>
> .
>

On Wed, 24 Feb 2010 11:43:01 -0800, MTate <(E-Mail Removed)>
wrote:

>This was tested today and it did not work. For some reason in the SQL
>(Access 2007) once the statement is saved it inserts a ' in the SQL. So now
>it reads BETWEEN ' #02/14/2010# and #02/14/2010#
>
>Any ideas? This does not occur in Access 2003.

Have you perchance defined this query as a PassThrough Query (to SQL/Server,
which does use ' as a date delimiter)?
--

John W. Vinson [MVP]