0

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.

Community
  • 1
  • 1
Tom TK
  • 65
  • 9
  • 1
    `Like` is for text. For numbers, use `[Street Number] = 10`. – Gustav Dec 17 '21 at 11:37
  • So Street number can't be exact, because on the chance I only want to find only Streets and Cities, The number field will be left blank and that criteria will look for records with blank fields. I need an IF statement or a is not blank argument, but I don't know how to implement it. – Tom TK Dec 17 '21 at 11:43
  • Well, you wrote: _the street number needs to be exact_. So ..? – Gustav Dec 17 '21 at 11:47
  • I also wrote this: AND the query must ignore the criteria and still pull the street and city if house number field is left empty, I made sure it was bold too, so..? – Tom TK Dec 17 '21 at 11:55
  • 3
    _So Street number can't be exact_ .. it can't both be exact and not exact. Do you mean _exact_ or _empty (Null)_? – Gustav Dec 17 '21 at 12:21
  • Youtube link didn't work so no way to answer because we don't know your address format.. But for the query part you just combine several simple queries into a big query. for instance in pseudo code: if isnull(TextBox1) and not isnull(TextBox2) and not isnull(TextBox3) then (Like "*" & [Textbox2] & "*") AND (Like "*" & [Textbox3] & "*") For each combination of null textboxes enumerate the simple query and combine into one big query. – mazoula Dec 23 '21 at 06:46

1 Answers1

0

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

Jeremy
  • 123
  • 6