Re: SQL query fails

From: MGFoster (me_at_privacy.com)
Date: 08/02/04 

Date: Mon, 02 Aug 2004 18:37:19 GMT

-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQQ6J1oechKqOuFEgEQJO+wCg0CS3xN5VK7CIchd1L0SIbMaz3K0AoPm1
oi27wjYZCoy8XKU0+AekmyO3
=fAcx
-----END PGP SIGNATURE-----
Joe Stanton wrote:
> 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.

Relevant Pages

  • Re: SELECT DISTINCT slow, how can I speed up
    ... We have staff using Access 2003 as a front end to the Oracle tables ... for the purposes of ad hoc queries. ... come back within a second of the SQL starting to run. ... only 20-50 rows come back and the query takes 28 seconds and there is ...
    (comp.databases.oracle.server)
  • Re: SQL -> Oracle
    ... > 2- extracted all my SQL queries in one single file (that was the tedious ... > 3- now whenever I need new query. ... > - Sybase and SQL server prefix their parameters with '@' ... > - Firebird and Oracle have selectable stored procedures so you use them ...
    (borland.public.delphi.non-technical)
  • Re: ORA-1866 The datetime class is invalid error...the saga continues!
    ... but you could try using a different data provider. ... Use the Microsoft ADO.NET Oracle Provider ... VB.NET to my employers and not being able to query data using a date field ... SQL statements I provided earlier will execute flawlessly in any enviornment ...
    (microsoft.public.dotnet.general)
  • Re: Linked Table Password
    ... I have a large amount of code that uses dynamic SQL. ... then change the SQL property of the query to match the current dynamic SQL ... >I assume that you are using queries to access the data in the SQL Server ... It is directed for Oracle users, ...
    (microsoft.public.access.security)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)