Query to filter a table where a field can be a number or left empty

Question

I created a form with 3 textboxes & a search button. Also a query that is setup to take the criteria in the textboxes & filter the results of a table with 1000+ records.

I followed this YouTube tutorial: watch?v=CTiA_4Me0cI&t=379s

It works to some degree, but I need to search a Street number, Address & City, not 3 strings. The criteria I was instructed to use:

Like "*" & [Forms]![SearchForm]![Textbox1] & "*"

If I search the word "Baker", the query finds all results that contain the string "Baker" as there are 2 wildcards on each side.

I want the query to search if only 1 or 2 of the 3 textboxes are filled. This has to run even if the textboxes are blank, but not look for blank fields, just ignore them.

My issue:
The like argument doesn't work with integers. I'll search "10" "baker" "london" for example, and I'll get results 1 Baker, 11 Baker, 21 Baker and so on.

I would like to search with 'Like' on the street and City.
The street number needs to be exact, except if the house number field is left empty, then ignore the criteria.

Answer 1

Using the query criteria below for each of your text boxes will return values, even if one or more text boxes are left blank.

Street number:

=[Forms]![SearchForm]![StreetNumberTextBox] Or [Forms]![SearchForm]![StreetNumberTextBox] Is Null

Address:

=[Forms]![SearchForm]![AddressTextBox] Or [Forms]![SearchForm]![AddressTextBox] Is Null

City:

=[Forms]![SearchForm]![CityTextBox] Or [Forms]![SearchForm]![CityTextBox] Is Null