467,868 Members | 1,296 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,868 developers. It's quick & easy.

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
  • viewed: 6508
3 Replies
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
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
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.