Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search for wildcard character (Access 2002 sp1)

    I am needing to query an existing database and strip out addresses that have been flagged as invalid. The problem is that procedure to flag an address as invalid is to put an asterisk in front of the address. This means that I must search for a wildcard character.

    I have used the criteria <font color=blue><font face="Comic Sans MS">"not like "[*]*"</font face=comic></font color=blue> in order to "not pull" the invalid addresses. This works, but the query takes about 3 minutes to run. If I remove this criteria, it runs in under 3 seconds. Does anyone have any alternative?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Search for wildcard character (Access 2002 sp1)

    Try the following:

    In your query, if Field1 contains the addresses, Select Field1 and Left(Field1,1) (as a separate field)
    In the criteria for Left(Field1,1) put <> Chr(42)

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Search for wildcard character (Access 2002 sp1)

    You could also use InStr function with Left function to test for asterisk. Example:

    SELECT Table1.Field1
    FROM Table1
    WHERE (((InStr(1,Left([Field1],1),"*",0))=0));

    With InStr you can use binary comparison ("0" as last argument) which is usually faster than equivalent textual comparison, but unless your query is evaluating zillions of records it's probably about same difference as using Left function alone....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •