471,093 Members | 1,929 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,093 software developers and data experts.

Ignoring criteria in a query if the field is Null


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]


memo field criteria: Like ("*" & [Forms]![QBF_Form]![summary] & "*")
Jul 3 '07 #1
3 6877
3,080 Expert 2GB
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.

Jul 3 '07 #2
Thank you Nico, that tip helped me fix the problem!

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


Jul 5 '07 #3
3,080 Expert 2GB
Check out my description I give my users:

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

Jul 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Vince Morgan | last post: by
7 posts views Thread by keliie | last post: by
2 posts views Thread by Mark Roughton | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.