Trying to use SQL WHERE stuff LIKE stuff command, Not working

[racemadnss]

Im creating a form to search a database

The form has several unbound fields you could use to search
The results show in an unbound Listbox

StreetAddress, Lot,Block,Section,Subdivision

I want the user to be able to type in
Kellywood

And recieve all results LIKE kellywood
Kellywood Point
Kellywood Terrace
Kellywoodville

Right now I have this:

Like [Forms]![Find Job]![Subdivision]

I can only Get "Kellywood Point" If I type in "Kellywood Point" Exactly

Thanks for the Help

BTW: Access 2000

 

[RichO]

Try:

Like "*" & [Forms]![Find Job]![Subdivision] & "*"

 

[racemadnss]

Ok, I Just tried that, and that seems to be a little too much, It would just include everything and I couldn't narrow it down.

Whats the easiest way to have the form create the SQL script so I can use AND on the fields that are "Dirty" and ig nore the other fields that are empty

b/c if they only type in a street address I dont want it looking for an entry with "Street name" AND "[empty city field]" b/c there wont be any with a street name and no city.

Thanks

 

[RichO]

As long as you use the wildcard * in the criteria, the query will return all records if nothing is specified in the criteria fields on your form.

For example, say you were to have criteria for the city field as :

Like "*" & Forms!MyForm!txtCity & "*"

and criteria for the street address field as :

Like "*" & Forms!MyForm!txtAddress & "*"

If the user inputs Los Angeles for the city and leaves the address field blank, the query will return all records with Los Angeles regardless of the address.

Simply put, the query is only going to look for criteria in those fields where criteria is specified, but again, only if you use the wildcard *.