Search Form not showing all results

[Rushman21]

I’m running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with
Description
CarNum
SerialNum
Category
Condition
In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum,serialnum, category…etc)
The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn’t work. I take out the “Like” criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields. Hope this makes sense and any help would be appreciated.
Thanks

 

[CJ_London]

suggest show the whole sql - showing just a bit doesn't help us to help you.

Also please clarify what you mean by 'I tried to use Is Null and Is Not Null but still doesn’t work' - on what? - the field in the table of the control on the form? or both?

 

[GinaWhipp]

Towards the bottom of the page (see link below) you will see how to do this in a query...
http://allenbrowne.com/ser-62.html

 

[Rushman21]

Thanks GinaWhipp! Looks like the query way is not the best way to do this. I will research a different/better way to do this multiple search form. Thank you for the link.

 

[Rogueci5]

Hey guys Thanks for this I was looking for a solution, I may have to come back with a few questions tho, I am not real bright

 

[GinaWhipp]

No problem and if you are looking for a better, download the example it shows how to do it with code which is much more flexible.