Re: Different result of LIKE and LEFT query in mdb file

From: Victor Koch ("Victor)
Date: 11/26/04 

Date: Fri, 26 Nov 2004 13:06:36 -0300

Try this

             Statement1:

SELECT * FROM [Order] WHERE OrderID LIKE 'C%' AND PrdtName LIKE '%'

             Statement2:

SELECT * FROM [Order] WHERE LEFT(OrderID,1) = 'C' AND PrdtName LIKE '%'

Or

SELECT * FROM [Order] WHERE OrderID LIKE 'C%' AND PrdtName IS NOT NULL 

--
 Víctor Koch.
"EricYuh Taiwan" <EricYuh Taiwan@discussions.microsoft.com> escribió en el
mensaje news:1B8125D4-7BD9-4503-A6BC-A3D094457670@microsoft.com...
> I have a database file what have some problem.
>
> Please check the follow statement
>             Statement1:         SELECT * FROM [Order] WHERE OrderID LIKE
> 'C*' AND PrdtName LIKE '*'
>             Statement2:        SELECT * FROM [Order] WHERE LEFT(OrderID,1)
=
> 'C' AND PrdtName LIKE '*'
>
> The "Statement2" can get correct result, but "Statement1" return no
record.
>
>
> And pleace check the follow statement
>
>             Statement3:         SELECT * FROM [Order] WHERE OrderID LIKE
> 'D*' AND PrdtName LIKE '*'
>             Statement4:        SELECT * FROM [Order] WHERE LEFT(OrderID,1)
=
> 'D' AND PrdtName LIKE '*'
>
> This two statement can get correct result both.
>
> My database file is Access97 format.
> If you try to open with Access2000 (or JET4.0 on ADO), All of statements
can
> return correct result.
>
>
> This problem seem include follow factor:
>         1.Data table Index
>         2.The differentness of VB function (LEFT) and ANSI-SQL (LIKE)
>         3.The differentness of JET 3.51(include DAO) and JET4.0
>
> But I still don't know this problem.
> Please help me and give some advise
> (sorry for my poor English)
> Best regard
> Eric
>
> The sample database file downlod address
> http://ericyuh.myweb.hinet.net/order.mdb  (3.1MB)
> http://ericyuh.myweb.hinet.net/order.exe (341KB, The same database file
but
> RAR SFX format)
>

Relevant Pages