Ignoring criteria in a query if the field is Null

[seagullino]

Hello,

I've developed my first Form, a simple affair that enables the user to search text in the memo fields in our database. When the "search" button is pressed, it runs a macro which runs the simple query.

I have added a second field to the form, in addition to the text search field. This new field allows the user to type in an identification number, which returns all of the memo fields associated with that particular client. If the user enters an ID number in the ID field of the form as well as a search term in the text search field of the form, it returns all memo fields of that particular client which contain that particular search term.

So far so good. But, the problem:

Now that I've added the ID field, if the user enters ONLY a memo search term and gives no value to the ID field, they get no results because the query is looking for ID field = Null and none of our ID fields are Null.

Is there a way to set this up so that the query is aware that just because a value is not entered into the ID field, the user still wants to see the results of the memo text search (which would apply to all ID numbers)?

Thank you so much for your advice!

- John

MS Access Version: 10.6501.6626

id_number field criteria: [Forms]![QBF_Form]![id_number]

and

memo field criteria: Like ("*" & [Forms]![QBF_Form]![summary] & "*")

[nico5038]

Just use for the ID also a LIKE * [ID] *, thus when there's no ID it will search for LIKE ** and select all.

Personally I never code this. I instruct my users to use the right-click pop-up form and they can thus perform every filtering they need on all fields, even a LIKE with a joker (*) as that can be entered in the third option's textbox.

Nic;o)

[seagullino]

Thank you Nico, that tip helped me fix the problem!

What is the right-click pop-up form that you refer to?

Thanks,

John

[nico5038]

Check out my description I give my users:
http://www.geocities.com/nico5038/xR...nstruction.zip

Took me a while to discover the possibilities, but I use it all the time to filter tables and/or forms.

Nic;o)