SQL query fails

[Joe Stanton]

Hello Group

I have a query that works in Oracle and SQL Server, but fails in Microsoft
Access.

The query is:

SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''

Here is my sample data:

TABLE1.DATA
Row1 A&M Stores
Row2 A&P Grocery
Row3 Assoc. Foods

Under Oracle and SQL Server the rows that are returned are Rows 1 and 2.
Under Access no rows are returned.

The goal is to write a SQL statement that works on all 3 platforms without
creating a customized query for each platform (or actually custom just for
Access). Please note that I know how to write a query that would work in
Access, but that query would use the InStr function which is not universally
available.

It is my theory that when Access (or Jet) executes the query it internally
executes the query as a LIKE type expression, and seeing that there are
special characters in the literal string this causes the query to return no
rows.

Thank you!
Joe

PS: Anyone emailing me responses are appreciated

[MGFoster]

Re: SQL query fails

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't use the same wild card character in all db engines. The "one
or more characters" wild card character in MS SQL & Oracle is the
ampersand (&); Access (JET) uses the asterisk (*). The "any one
character" wild card in MS SQL & Oracle is the underline (_); Access
(JET) uses the question-mark (?).

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6J1oechKq OuFEgEQJO+wCg0C S3xN5VK7CIchd1L 0SIbMaz3K0AoPm1
oi27wjYZCoy8XKU 0+AekmyO3
=fAcx
-----END PGP SIGNATURE-----


Joe Stanton wrote:
[color=blue]
> Hello Group
>
> I have a query that works in Oracle and SQL Server, but fails in Microsoft
> Access.
>
> The query is:
>
> SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
>
> Here is my sample data:
>
> TABLE1.DATA
> Row1 A&M Stores
> Row2 A&P Grocery
> Row3 Assoc. Foods
>
> Under Oracle and SQL Server the rows that are returned are Rows 1 and 2.
> Under Access no rows are returned.
>
> The goal is to write a SQL statement that works on all 3 platforms without
> creating a customized query for each platform (or actually custom just for
> Access). Please note that I know how to write a query that would work in
> Access, but that query would use the InStr function which is not universally
> available.
>
> It is my theory that when Access (or Jet) executes the query it internally
> executes the query as a LIKE type expression, and seeing that there are
> special characters in the literal string this causes the query to return no
> rows.[/color]

[Joe Stanton]

Re: SQL query fails

Thank you for the response, but the issue here is not using wildcards but
just finding data.


"MGFoster" <me@privacy.com > wrote in message
news:zRvPc.6800 $9Y6.1495@newsr ead1.news.pas.e arthlink.net...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> You can't use the same wild card character in all db engines. The "one
> or more characters" wild card character in MS SQL & Oracle is the
> ampersand (&); Access (JET) uses the asterisk (*). The "any one
> character" wild card in MS SQL & Oracle is the underline (_); Access
> (JET) uses the question-mark (?).
>
> --
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQQ6J1oechKq OuFEgEQJO+wCg0C S3xN5VK7CIchd1L 0SIbMaz3K0AoPm1
> oi27wjYZCoy8XKU 0+AekmyO3
> =fAcx
> -----END PGP SIGNATURE-----
>
>
> Joe Stanton wrote:
>[color=green]
> > Hello Group
> >
> > I have a query that works in Oracle and SQL Server, but fails in[/color][/color]
Microsoft[color=blue][color=green]
> > Access.
> >
> > The query is:
> >
> > SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
> >
> > Here is my sample data:
> >
> > TABLE1.DATA
> > Row1 A&M Stores
> > Row2 A&P Grocery
> > Row3 Assoc. Foods
> >
> > Under Oracle and SQL Server the rows that are returned are Rows 1 and 2.
> > Under Access no rows are returned.
> >
> > The goal is to write a SQL statement that works on all 3 platforms[/color][/color]
without[color=blue][color=green]
> > creating a customized query for each platform (or actually custom just[/color][/color]
for[color=blue][color=green]
> > Access). Please note that I know how to write a query that would work[/color][/color]
in[color=blue][color=green]
> > Access, but that query would use the InStr function which is not[/color][/color]
universally[color=blue][color=green]
> > available.
> >
> > It is my theory that when Access (or Jet) executes the query it[/color][/color]
internally[color=blue][color=green]
> > executes the query as a LIKE type expression, and seeing that there are
> > special characters in the literal string this causes the query to return[/color][/color]
no[color=blue][color=green]
> > rows.[/color]
>[/color]

[Van T. Dinh]

Re: SQL query fails

It looks to me that "A" < "A&" < "A&M ..." < "Assoc...", (in most of
alphabetical ordering methods and at least in JET alphabetical ordering).

so none of your Records meet the criteria

(Data >= 'A&') AND (Data < 'A')

(a "circular" greater than relationship which is impossible.)

and therefore none is selected.

--
HTH
Van T. Dinh
MVP (Access)




"Joe Stanton" <joe@bravenewso ftware.com> wrote in message
news:ZSuPc.1029 29$QI2.43690@ne wssvr29.news.pr odigy.com...[color=blue]
> Hello Group
>
> I have a query that works in Oracle and SQL Server, but fails in Microsoft
> Access.
>
> The query is:
>
> SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
>
> Here is my sample data:
>
> TABLE1.DATA
> Row1 A&M Stores
> Row2 A&P Grocery
> Row3 Assoc. Foods
>
> Under Oracle and SQL Server the rows that are returned are Rows 1 and 2.
> Under Access no rows are returned.
>
> The goal is to write a SQL statement that works on all 3 platforms without
> creating a customized query for each platform (or actually custom just for
> Access). Please note that I know how to write a query that would work in
> Access, but that query would use the InStr function which is not[/color]
universally[color=blue]
> available.
>
> It is my theory that when Access (or Jet) executes the query it internally
> executes the query as a LIKE type expression, and seeing that there are
> special characters in the literal string this causes the query to return[/color]
no[color=blue]
> rows.
>
> Thank you!
> Joe
>
> PS: Anyone emailing me responses are appreciated
>
>[/color]

[Bob Quintal]

Re: SQL query fails

"Joe Stanton" <joe@bravenewso ftware.com> wrote in
news:ZSuPc.1029 29$QI2.43690@ne wssvr29.news.pr odigy.com:
[color=blue]
> Hello Group
>
> I have a query that works in Oracle and SQL Server, but fails
> in Microsoft Access.
>
> The query is:
>
> SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
>
> Here is my sample data:
>
> TABLE1.DATA
> Row1 A&M Stores
> Row2 A&P Grocery
> Row3 Assoc. Foods
>
> Under Oracle and SQL Server the rows that are returned are
> Rows 1 and 2. Under Access no rows are returned.
>
> The goal is to write a SQL statement that works on all 3
> platforms without creating a customized query for each
> platform (or actually custom just for Access). Please note
> that I know how to write a query that would work in Access,
> but that query would use the InStr function which is not
> universally available.
>
> It is my theory that when Access (or Jet) executes the query
> it internally executes the query as a LIKE type expression,
> and seeing that there are special characters in the literal
> string this causes the query to return no rows.
>
> Thank you!
> Joe
>
> PS: Anyone emailing me responses are appreciated
>
>[/color]

I touched up your query to explain what I think is going on:,
SELECT data from TABLE1 WHERE data>=65+38 AND data<65

Seems to me that nothing should get returned in any version.
However, the <65 (the ascii value for A) may automatically be
doing a left() in SQL server and Oracle.

Using WHERE data LIKE "A&*" will work.

--
Bob Quintal

PA is y I've altered my email address.

[Joe Stanton]

Re: SQL query fails

It may have escaped notice, but the second operator in the string has 3
trailing single quotes, meaning that the data in the second operator is
actually (put on one line below for clarity):

A'

and so the properly constructed literal string in SQL is

'A'''

This SQL is constructed in this way due to the need to create a statement
that is acceptable (without tweaking) to Oracle, SQL Server, and Access. So
this requires using the single quote as the literal string delimiter as well
as not relying on LIKE (which has different wildcard characters for Access
and Oracle/SQL) or perhaps Instr, etc.

The user inputs the data A&. The program determines the next ASCII
character in sequence after the last character in the input (the &), and in
thise case determines it to be the single quote. And so the purpose is to
find all strings that start with A&.



"Bob Quintal" <rquintal@sPAmp atico.ca> wrote in message
news:Xns953A6EA 825617BQuintal@ 66.150.105.49.. .[color=blue]
> "Joe Stanton" <joe@bravenewso ftware.com> wrote in
> news:ZSuPc.1029 29$QI2.43690@ne wssvr29.news.pr odigy.com:
>[color=green]
> > Hello Group
> >
> > I have a query that works in Oracle and SQL Server, but fails
> > in Microsoft Access.
> >
> > The query is:
> >
> > SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
> >
> > Here is my sample data:
> >
> > TABLE1.DATA
> > Row1 A&M Stores
> > Row2 A&P Grocery
> > Row3 Assoc. Foods
> >
> > Under Oracle and SQL Server the rows that are returned are
> > Rows 1 and 2. Under Access no rows are returned.
> >
> > The goal is to write a SQL statement that works on all 3
> > platforms without creating a customized query for each
> > platform (or actually custom just for Access). Please note
> > that I know how to write a query that would work in Access,
> > but that query would use the InStr function which is not
> > universally available.
> >
> > It is my theory that when Access (or Jet) executes the query
> > it internally executes the query as a LIKE type expression,
> > and seeing that there are special characters in the literal
> > string this causes the query to return no rows.
> >
> > Thank you!
> > Joe
> >
> > PS: Anyone emailing me responses are appreciated
> >
> >[/color]
>
> I touched up your query to explain what I think is going on:,
> SELECT data from TABLE1 WHERE data>=65+38 AND data<65
>
> Seems to me that nothing should get returned in any version.
> However, the <65 (the ascii value for A) may automatically be
> doing a left() in SQL server and Oracle.
>
> Using WHERE data LIKE "A&*" will work.
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.[/color]

[John Spencer (MVP)]

Re: SQL query fails

I just tried it in ACCESS 97 and it worked for me there. It returned two
records that started with A& and didn't return Anderson.

SELECT Text
FROM Test
WHERE Text>'A&' And Text<'A'''

So, what version of Access? What data engine (MSDE or JET)?

I believe that you will find some problems with later versions of Access and
Jet. MS decided to change the sort tables and some things just don't work as
they used to.


Joe Stanton wrote:[color=blue]
>
> It may have escaped notice, but the second operator in the string has 3
> trailing single quotes, meaning that the data in the second operator is
> actually (put on one line below for clarity):
>
> A'
>
> and so the properly constructed literal string in SQL is
>
> 'A'''
>
> This SQL is constructed in this way due to the need to create a statement
> that is acceptable (without tweaking) to Oracle, SQL Server, and Access. So
> this requires using the single quote as the literal string delimiter as well
> as not relying on LIKE (which has different wildcard characters for Access
> and Oracle/SQL) or perhaps Instr, etc.
>
> The user inputs the data A&. The program determines the next ASCII
> character in sequence after the last character in the input (the &), and in
> thise case determines it to be the single quote. And so the purpose is to
> find all strings that start with A&.
>
> "Bob Quintal" <rquintal@sPAmp atico.ca> wrote in message
> news:Xns953A6EA 825617BQuintal@ 66.150.105.49.. .[color=green]
> > "Joe Stanton" <joe@bravenewso ftware.com> wrote in
> > news:ZSuPc.1029 29$QI2.43690@ne wssvr29.news.pr odigy.com:
> >[color=darkred]
> > > Hello Group
> > >
> > > I have a query that works in Oracle and SQL Server, but fails
> > > in Microsoft Access.
> > >
> > > The query is:
> > >
> > > SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
> > >
> > > Here is my sample data:
> > >
> > > TABLE1.DATA
> > > Row1 A&M Stores
> > > Row2 A&P Grocery
> > > Row3 Assoc. Foods
> > >
> > > Under Oracle and SQL Server the rows that are returned are
> > > Rows 1 and 2. Under Access no rows are returned.
> > >
> > > The goal is to write a SQL statement that works on all 3
> > > platforms without creating a customized query for each
> > > platform (or actually custom just for Access). Please note
> > > that I know how to write a query that would work in Access,
> > > but that query would use the InStr function which is not
> > > universally available.
> > >
> > > It is my theory that when Access (or Jet) executes the query
> > > it internally executes the query as a LIKE type expression,
> > > and seeing that there are special characters in the literal
> > > string this causes the query to return no rows.
> > >
> > > Thank you!
> > > Joe
> > >
> > > PS: Anyone emailing me responses are appreciated
> > >
> > >[/color]
> >
> > I touched up your query to explain what I think is going on:,
> > SELECT data from TABLE1 WHERE data>=65+38 AND data<65
> >
> > Seems to me that nothing should get returned in any version.
> > However, the <65 (the ascii value for A) may automatically be
> > doing a left() in SQL server and Oracle.
> >
> > Using WHERE data LIKE "A&*" will work.
> >
> > --
> > Bob Quintal
> >
> > PA is y I've altered my email address.[/color][/color]

[Joe Stanton]

Re: SQL query fails

Interesting... I am using Access 2000 for this test, and in the VB
application it is via DAO/Jet 3.6. Thanks for the info that it used to work
and now doesn't - sheds some light on the confusion.

So, returning to the oroginal question, how then can a query be crafted that
will work correctly on these 3 platforms (Oracle 8i/9i, SQL Server 7/2000,
Access 97/2000/XP/2003)?

I had the idea of using the Left function, but have not yet determined it's
implementation on the enterprise backends.



"John Spencer (MVP)" <spencer4@comca st.net> wrote in message
news:410FFBA0.3 55BD4FC@comcast .net...[color=blue]
> I just tried it in ACCESS 97 and it worked for me there. It returned two
> records that started with A& and didn't return Anderson.
>
> SELECT Text
> FROM Test
> WHERE Text>'A&' And Text<'A'''
>
> So, what version of Access? What data engine (MSDE or JET)?
>
> I believe that you will find some problems with later versions of Access[/color]
and[color=blue]
> Jet. MS decided to change the sort tables and some things just don't work[/color]
as[color=blue]
> they used to.
>
>
> Joe Stanton wrote:[color=green]
> >
> > It may have escaped notice, but the second operator in the string has 3
> > trailing single quotes, meaning that the data in the second operator is
> > actually (put on one line below for clarity):
> >
> > A'
> >
> > and so the properly constructed literal string in SQL is
> >
> > 'A'''
> >
> > This SQL is constructed in this way due to the need to create a[/color][/color]
statement[color=blue][color=green]
> > that is acceptable (without tweaking) to Oracle, SQL Server, and Access.[/color][/color]
So[color=blue][color=green]
> > this requires using the single quote as the literal string delimiter as[/color][/color]
well[color=blue][color=green]
> > as not relying on LIKE (which has different wildcard characters for[/color][/color]
Access[color=blue][color=green]
> > and Oracle/SQL) or perhaps Instr, etc.
> >
> > The user inputs the data A&. The program determines the next ASCII
> > character in sequence after the last character in the input (the &), and[/color][/color]
in[color=blue][color=green]
> > thise case determines it to be the single quote. And so the purpose is[/color][/color]
to[color=blue][color=green]
> > find all strings that start with A&.
> >
> > "Bob Quintal" <rquintal@sPAmp atico.ca> wrote in message
> > news:Xns953A6EA 825617BQuintal@ 66.150.105.49.. .[color=darkred]
> > > "Joe Stanton" <joe@bravenewso ftware.com> wrote in
> > > news:ZSuPc.1029 29$QI2.43690@ne wssvr29.news.pr odigy.com:
> > >
> > > > Hello Group
> > > >
> > > > I have a query that works in Oracle and SQL Server, but fails
> > > > in Microsoft Access.
> > > >
> > > > The query is:
> > > >
> > > > SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
> > > >
> > > > Here is my sample data:
> > > >
> > > > TABLE1.DATA
> > > > Row1 A&M Stores
> > > > Row2 A&P Grocery
> > > > Row3 Assoc. Foods
> > > >
> > > > Under Oracle and SQL Server the rows that are returned are
> > > > Rows 1 and 2. Under Access no rows are returned.
> > > >
> > > > The goal is to write a SQL statement that works on all 3
> > > > platforms without creating a customized query for each
> > > > platform (or actually custom just for Access). Please note
> > > > that I know how to write a query that would work in Access,
> > > > but that query would use the InStr function which is not
> > > > universally available.
> > > >
> > > > It is my theory that when Access (or Jet) executes the query
> > > > it internally executes the query as a LIKE type expression,
> > > > and seeing that there are special characters in the literal
> > > > string this causes the query to return no rows.
> > > >
> > > > Thank you!
> > > > Joe
> > > >
> > > > PS: Anyone emailing me responses are appreciated
> > > >
> > > >
> > >
> > > I touched up your query to explain what I think is going on:,
> > > SELECT data from TABLE1 WHERE data>=65+38 AND data<65
> > >
> > > Seems to me that nothing should get returned in any version.
> > > However, the <65 (the ascii value for A) may automatically be
> > > doing a left() in SQL server and Oracle.
> > >
> > > Using WHERE data LIKE "A&*" will work.
> > >
> > > --
> > > Bob Quintal
> > >
> > > PA is y I've altered my email address.[/color][/color][/color]

[John Spencer (MVP)]

Re: SQL query fails

Don't know what will work.

Perhaps you could use something like:

WHERE Text >'A&' AND Text <'A&ZZZZZZZ'

By the way, I just confirmed (as if you needed it) that I get the same behavior
as you in Access 2000

Joe Stanton wrote:[color=blue]
>
> Interesting... I am using Access 2000 for this test, and in the VB
> application it is via DAO/Jet 3.6. Thanks for the info that it used to work
> and now doesn't - sheds some light on the confusion.
>
> So, returning to the oroginal question, how then can a query be crafted that
> will work correctly on these 3 platforms (Oracle 8i/9i, SQL Server 7/2000,
> Access 97/2000/XP/2003)?
>
> I had the idea of using the Left function, but have not yet determined it's
> implementation on the enterprise backends.
>
> "John Spencer (MVP)" <spencer4@comca st.net> wrote in message
> news:410FFBA0.3 55BD4FC@comcast .net...[color=green]
> > I just tried it in ACCESS 97 and it worked for me there. It returned two
> > records that started with A& and didn't return Anderson.
> >
> > SELECT Text
> > FROM Test
> > WHERE Text>'A&' And Text<'A'''
> >
> > So, what version of Access? What data engine (MSDE or JET)?
> >
> > I believe that you will find some problems with later versions of Access[/color]
> and[color=green]
> > Jet. MS decided to change the sort tables and some things just don't work[/color]
> as[color=green]
> > they used to.
> >
> >
> > Joe Stanton wrote:[color=darkred]
> > >
> > > It may have escaped notice, but the second operator in the string has 3
> > > trailing single quotes, meaning that the data in the second operator is
> > > actually (put on one line below for clarity):
> > >
> > > A'
> > >
> > > and so the properly constructed literal string in SQL is
> > >
> > > 'A'''
> > >
> > > This SQL is constructed in this way due to the need to create a[/color][/color]
> statement[color=green][color=darkred]
> > > that is acceptable (without tweaking) to Oracle, SQL Server, and Access.[/color][/color]
> So[color=green][color=darkred]
> > > this requires using the single quote as the literal string delimiter as[/color][/color]
> well[color=green][color=darkred]
> > > as not relying on LIKE (which has different wildcard characters for[/color][/color]
> Access[color=green][color=darkred]
> > > and Oracle/SQL) or perhaps Instr, etc.
> > >
> > > The user inputs the data A&. The program determines the next ASCII
> > > character in sequence after the last character in the input (the &), and[/color][/color]
> in[color=green][color=darkred]
> > > thise case determines it to be the single quote. And so the purpose is[/color][/color]
> to[color=green][color=darkred]
> > > find all strings that start with A&.
> > >
> > > "Bob Quintal" <rquintal@sPAmp atico.ca> wrote in message
> > > news:Xns953A6EA 825617BQuintal@ 66.150.105.49.. .
> > > > "Joe Stanton" <joe@bravenewso ftware.com> wrote in
> > > > news:ZSuPc.1029 29$QI2.43690@ne wssvr29.news.pr odigy.com:
> > > >
> > > > > Hello Group
> > > > >
> > > > > I have a query that works in Oracle and SQL Server, but fails
> > > > > in Microsoft Access.
> > > > >
> > > > > The query is:
> > > > >
> > > > > SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
> > > > >
> > > > > Here is my sample data:
> > > > >
> > > > > TABLE1.DATA
> > > > > Row1 A&M Stores
> > > > > Row2 A&P Grocery
> > > > > Row3 Assoc. Foods
> > > > >
> > > > > Under Oracle and SQL Server the rows that are returned are
> > > > > Rows 1 and 2. Under Access no rows are returned.
> > > > >
> > > > > The goal is to write a SQL statement that works on all 3
> > > > > platforms without creating a customized query for each
> > > > > platform (or actually custom just for Access). Please note
> > > > > that I know how to write a query that would work in Access,
> > > > > but that query would use the InStr function which is not
> > > > > universally available.
> > > > >
> > > > > It is my theory that when Access (or Jet) executes the query
> > > > > it internally executes the query as a LIKE type expression,
> > > > > and seeing that there are special characters in the literal
> > > > > string this causes the query to return no rows.
> > > > >
> > > > > Thank you!
> > > > > Joe
> > > > >
> > > > > PS: Anyone emailing me responses are appreciated
> > > > >
> > > > >
> > > >
> > > > I touched up your query to explain what I think is going on:,
> > > > SELECT data from TABLE1 WHERE data>=65+38 AND data<65
> > > >
> > > > Seems to me that nothing should get returned in any version.
> > > > However, the <65 (the ascii value for A) may automatically be
> > > > doing a left() in SQL server and Oracle.
> > > >
> > > > Using WHERE data LIKE "A&*" will work.
> > > >
> > > > --
> > > > Bob Quintal
> > > >
> > > > PA is y I've altered my email address.[/color][/color][/color]

[Joe Stanton]

Re: SQL query fails

BTW, thanks for the assistance. I am glad for the confirmation. Will get
this idea for the Left check tested later. The developer I am assisting
went to the dentist and will be back later tomorrow.



"John Spencer (MVP)" <spencer4@comca st.net> wrote in message
news:411034AF.8 B19AFCF@comcast .net...[color=blue]
> Don't know what will work.
>
> Perhaps you could use something like:
>
> WHERE Text >'A&' AND Text <'A&ZZZZZZZ'
>
> By the way, I just confirmed (as if you needed it) that I get the same[/color]
behavior[color=blue]
> as you in Access 2000
>[/color]

[Bob Quintal]

Re: SQL query fails

"Joe Stanton" <joe@bravenewso ftware.com> wrote in
news:JhPPc.5970 $AY5.4055@newss vr21.news.prodi gy.com:
[color=blue]
> It may have escaped notice, but the second operator in the
> string has 3 trailing single quotes, meaning that the data in
> the second operator is actually (put on one line below for
> clarity):
>
> A'
>
> and so the properly constructed literal string in SQL is
>
> 'A'''
>[/color]
Sorry, I need glasses.

I played around with this and discovered that DATA <"A'" double
quotes also fails to return records, but going from ' to (, char 39
to 40 works., with the literal in single or double quotes, and can
be created from the query builder or in code..

I discovered that DATA <'A''' fails to return records.
I also found that DATA >='O''' returns O'Clare and O'Hare, as well
as everything above, but when coupled with AND DATA <"Oz" returns
nothing.

So it seems that when using the < or <=, something in Jet breaks.

This behaviour is in my opinion a "BUG".

--
Bob Quintal

PA is y I've altered my email address.

[Van T. Dinh]

Re: SQL query fails

Sorry that I missed the single-quote before.

This is possibly the behaviour of JET 4 (default engine in Access 2000)
which is different from JET 3.5 (A97).

There is an article on Microsoft Web site about this sorting. See:

http://support.microsoft.com/?id=236952

--
HTH
Van T. Dinh
MVP (Access)




"Joe Stanton" <joe@bravenewso ftware.com> wrote in message
news:OgUPc.6101 $AY5.98@newssvr 21.news.prodigy .com...[color=blue]
> Interesting... I am using Access 2000 for this test, and in the VB
> application it is via DAO/Jet 3.6. Thanks for the info that it used to[/color]
work[color=blue]
> and now doesn't - sheds some light on the confusion.
>
> So, returning to the oroginal question, how then can a query be crafted[/color]
that[color=blue]
> will work correctly on these 3 platforms (Oracle 8i/9i, SQL Server 7/2000,
> Access 97/2000/XP/2003)?
>
> I had the idea of using the Left function, but have not yet determined[/color]
it's[color=blue]
> implementation on the enterprise backends.
>
>
>[/color]

[Bri]

Re: SQL query fails

Joe,

I don't have AC2K to test this on, but what about using BETWEEN and a
not-equal combined? It is supported in both Access and SQL-Server (I
don't know about Oracle, but I would assume that it works there too).

SELECT data FROM TABLE1 WHERE (data BETWEEN 'A&' AND 'A''') AND data <>
'A'''

Bri

Joe Stanton wrote:[color=blue]
> Interesting... I am using Access 2000 for this test, and in the VB
> application it is via DAO/Jet 3.6. Thanks for the info that it used to work
> and now doesn't - sheds some light on the confusion.
>
> So, returning to the oroginal question, how then can a query be crafted that
> will work correctly on these 3 platforms (Oracle 8i/9i, SQL Server 7/2000,
> Access 97/2000/XP/2003)?
>
> I had the idea of using the Left function, but have not yet determined it's
> implementation on the enterprise backends.[/color]