Different result of LIKE and LEFT query in mdb file

From: EricYuh Taiwan (Taiwan_at_discussions.microsoft.com)
Date: 11/26/04 

Date: Thu, 25 Nov 2004 18:49:04 -0800

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