Different result of LEFT and LIKE query in mdb file

[Guest]

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, Access 97 database)

http://ericyuh.myweb.hinet.net/order.exe
(341KB, The same database file but RAR SFX format)

 

[JohnFol]

You have a password on the database

 

[Guest]

Thanks for reply and sorry for that password.

Now I have cancel the database password.

Thanks for your help.

You have a password on the database

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, Access 97 database)

http://ericyuh.myweb.hinet.net/order.exe
(341KB, The same database file but RAR SFX format)