Wild Cards for both text and numbers.... (1 Viewer)

[Gordon]

Hi,

I'm trying to search a field which has both names and numbers in it.

For purely text entries the wildcard search works just fine.... like this:

Like "*" & [What is Last Name?] & "*"

I can type in "Smi" and get all of the Smith, Smiths, etc.



However, when I try to use this for entries which include numbers (123), which should return "Smi123" I don't get any results at all.

Any suggestions? I can put in the SMI123 as straight text in the criteria box, and will get the appropriate record.... so I know it is there!

Thanks .....

Gordon

 

[RV]

http://www.access-programmers.co.uk/forums/showthread.php?t=98915

RV

 

[Gordon]

Like * & [Please enter address] & *

RV,

Thanks so much for the response. Your link took me to the thread where the format Like * & [Please enter address] & * was suggested. I already had tried that, but it was still not returning the desired results.

The solution I found involved simplifying the query which initially involved pulling information from two tables and another query. Even though the query should not have created a filter, it seemed to do so. I've had other experiences where queries produce strange results when too many fields are included.....

Gordon

 

[Pat Hartman]

I've had other experiences where queries produce strange results when too many fields are included.....

The problem is more likely that the selection criteria was being applied to the many-side table which was joined to with a left join. In this case, you need to create a query of only the many-side table that includes the criteria. Then join the main table to the query and supply any main table criteria.

 

[Gordon]

Thanks Pat! I'll give that a try!

Gordon