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

From: Val Mazur (group51a_at_hotmail.com)
Date: 11/27/04

  • Next message: Alex: "Append and Delete for Updatebatch" 
    Date: Fri, 26 Nov 2004 23:55:03 -0500

    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: Alex: "Append and Delete for Updatebatch"

    Relevant Pages

    • Re: Database Alternative
      ... > Its in a beta state and I want to inform you some test ... Hi bumerang, ... A query for one indexed object in 10,000 objects takes 1.1 seconds. ... The indexed database file has a size of 470kB. ...
      (microsoft.public.dotnet.framework.compactframework)
    • Re: Sessions count?
      ... Job notification exit points [as I infer] can be used to log start and stop timestamps. ... Logged to a database file, a query of the output could count the jobs that spanned a specific time. ...
      (comp.sys.ibm.as400.misc)
    • Re: Mail Merge query results with Office 2002
      ... I changed the select queries to a make table queries (user input ... Jerry M. Gartner ... I may end up doing a make table query - since only tables are there. ... Then choose your database file. ...
      (microsoft.public.access.queries)
    • Re: Make a report show content from an external Database
      ... You can create a query or modify the sql of a query to something like: ... This would allow you to create a record source for your report. ... So i have a database file with forms ... Dim qdf As QueryDef ...
      (microsoft.public.access.reports)
    • Re: Mail Merge query results with Office 2002
      ... sure why the queries aren't available but they aren't. ... database file, but the default file type is a Word document. ... Your next choice is to select the Table or Query containing the ...
      (microsoft.public.access.queries)