Search for wildcard character (Access 2002 sp1)

Topic

New Reply

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 “not like “[*]*” 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?

Reply | Quote

Replies

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

Reply | Quote

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….

Reply | Quote