Query Multiple Fields with One Entry Using Like

[gimmyd]

I am using a dialog box that takes one address entry and searches three fields for matching records. I want to be able to let the user enter only part of a street name and/or number and get back records that he could manually look at for the correct record. For Example, to find 67 DANIELS RD the user could enter 67 or 67 DAN or DAN. Here is the SQL I've tried so far but I get far too many hits. I'm using Access 97.

WHERE ((([Customer Information by Account Number].Owner_addr_1) Like '*' & [Forms]![Search Dialog]![OwnerAddress1])) & '*' OR ((([Customer Information by Account Number].Owner_addr_2) Like '*' & [Forms]![Search Dialog]![OwnerAddress1])) & '*'OR ((([Customer Information by Account Number].House_nbr) Like '*' & [Forms]![Search Dialog]![OwnerAddress1]) AND (([House_nbr] & " " & [StreetName])=[Forms]![Search Dialog]![OwnerAddress1]))&'*';

 

[mdthornton]

Are you getting duplicate rows back or are the results technically correct, ie they match your criteria? Best Regards,
Mike

 

[gimmyd]

I'm getting back every entry in the table no matter what the field contains.

 

[mdthornton]

Did you copy the SQL above from your query. There something not right with the bracketing which might be causing one or more of your OR clauses to be TRUE and therefore return all the rows in the table. Specifically, you have:

WHERE ((([Customer Information by Account Number].Owner_addr_1) Like '*' & [Forms]![Search Dialog]![OwnerAddress1])) & '*' ....

where I would expect:

WHERE ((([Customer Information by Account Number].Owner_addr_1) Like '*' & [Forms]![Search Dialog]![OwnerAddress1] & '*'))

Try stripping out all your like criteria and then add one clause back in at a time so you can monitor the results of each.

Best Regards,
Mike

 

[gimmyd]

I would like to thank you very much. Your solution was right, the '*' had to be inside the parentheses. I appreciate your help, Mike.

Regards,
Jim Dalton