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

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

  • Next message: EricYuh Taiwan: "Re: Different result of LIKE and LEFT query in mdb file" 
    Date: Sat, 27 Nov 2004 07:57:02 -0800

    Hi, Val Mazur! Thanks for your reply.
    I will try your advise on this Monday.

    Because the sample database file is Access97 format, and my application is
    build on DAO. I still used the * wildcard on DAO (and JET3.51), and I used
    the % wildcard on JET4.0 (and Access2000).

    Base on my examine, the problem what I describe previously are not exist in
    Access2000 or JET4.0. It mean that although the same database file but
    different of connection type (DAO, JET3.51, Access97, JET4.0 and Access2000),
    the result of the query is different.

    Not only the connection type will affect the correctness of the result, but
    the using of the filter on [OrderID]. Have you try to open this sample
    database on Access97 or DAO or JET3.51 connection ? As my question
    previously, the statement3 ([OrderID] LIKE 'D*') can get correct result but
    the statement1 ([OrderID] LIKE 'C*') can't. Certainly, it indeed exist such
    record in that sample database file.

    Another one testing is that, when I delete any one record that [OrderID]
    column is begin with 'C' character (ex:CA01500083). These result of two
    statements above become correctly.

    I have try to many way to measure this problem. Sometime I even suspect this
    problem is a bug of JET3.51 or Access97.

    I have successfully solve few problem of VB and Windows OS. But no one like
    this problem make me headache.

    "Val Mazur" wrote:

    > Hi,
    >
    > Try to use % wildcard instead of *. You also do not need second LIKE clause
    > (PrdtName LIKE '*'), because it selects all the values and does not affect
    > result, but affects performance of the query. Your
    > query should look like
    >
    > SELECT * FROM [Order] WHERE OrderID LIKE 'C%'
    >
    > --
    > Val Mazur
    > Microsoft MVP
    >
    >
    > "EricYuh Taiwan" <EricYuhTaiwan@discussions.microsoft.com> wrote in message
    > news:3FE4AC75-B726-4635-807F-894492502AF3@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 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)
    > >
    >
    >
    >

  • Next message: EricYuh Taiwan: "Re: Different result of LIKE and LEFT query in mdb file"

    Relevant Pages