Creating a User Friendly Search Interface

[Bhujanga]

I'm trying to design a form with a few key fields on it that will allow the user to enter any portion one or more of those fields (i.e. if they enter "Elm" in the address field, it would match all addresses on Elm Street, Elm Drive, etc.), and then upon clicking on a button it will bring up the list of all records that match the criteria. To do this I invoke a query that uses the 'like "*" & [field name] & "*"' syntax for each of the fields. The only problem is that if a field in the dataset is null, the record is excluded even if the the corresponding field on the search form was left blank.

So for example:
If they enter "Elm" in the address field, and enter Smith in the name field and leave the Phone Number field blank, it should bring up all the records for Smiths with Elm addresses, regardless of their phone number, even is the phone number field in the data set hasn't been filled in for some of the matches. What happens is that all records with null values are excluded.

Is there a better way to approach this, or is there a syntax that will avoid the problem?

Thank you.

[Rabbit]

Use Nz(FieldName, "") Like ...

[puppydogbuddy]

I'm trying to design a form with a few key fields on it that will allow the user to enter any portion one or more of those fields (i.e. if they enter "Elm" in the address field, it would match all addresses on Elm Street, Elm Drive, etc.), and then upon clicking on a button it will bring up the list of all records that match the criteria. To do this I invoke a query that uses the 'like "*" & [field name] & "*"' syntax for each of the fields. The only problem is that if a field in the dataset is null, the record is excluded even if the the corresponding field on the search form was left blank.

So for example:
If they enter "Elm" in the address field, and enter Smith in the name field and leave the Phone Number field blank, it should bring up all the records for Smiths with Elm addresses, regardless of their phone number, even is the phone number field in the data set hasn't been filled in for some of the matches. What happens is that all records with null values are excluded.

Is there a better way to approach this, or is there a syntax that will avoid the problem?

Thank you.

Yes, by building SQL substrings for each search item and then concatenating the substrings into one SQL string. Each substring is set to "" if the return to the substring is null;then before the compound string is about to be put to gether the fields with the empty strings are excluded from the composite string.

The link below is to an excellent multifield search facility with source code so that you can see how it works.

http://allenbrowne.com/ser-62.html

[Bhujanga]

Yes, by building SQL substrings for each search item and then concatenating the substrings into one SQL string. Each substring is set to "" if the return to the substring is null;then before the compound string is about to be put to gether the fields with the empty strings are excluded from the composite string.

The link below is to an excellent multifield search facility with source code so that you can see how it works.

http://allenbrowne.com/ser-62.html

Thank you. I think this will be very helpful.

[Bhujanga]

Use Nz(FieldName, "") Like ...

Hey, it worked! Thanks.

[Rabbit]

Hey, it worked! Thanks.

Not a problem, good luck.