Different result of LEFT and LIKE query in mdb file

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

Date: Thu, 25 Nov 2004 18:53:02 -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 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