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

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

Date: Sat, 27 Nov 2004 08:15:03 -0800

Hi Victor Koch, thanks your help!
I will try your advise on this Monday.

Because the database file is Access97 format and my application is built on
DAO, I must use * wildcard.

If I open the sample database file with Access 2000 (connect with JET4.0),
the problem that I describe above is not exist.

Thank your for your reply.

"Victor Koch" wrote:

> 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