Split Form-Multiple text boxes as like filters with null entries permitted

[TDC21]

I have a Query "TRACE_QUERY" with 4 fields [THICK] [PART_ID] [MTR] and [HEAT/SLAB]

I have created a Split Form "SEARCH_MTR" from the query above

In the form I have added 3 unbound text fields [MTR-SEARCH] [HEAT-SLAB SEARCH] and [THICK]

In the form I have added a button with the following macro


Apply Filter - Where = [SEARCH_MTR]![MTR] Like "*" & [Forms]![SEARCH_MTR]![Search-MTR] & "*"

and

Apply Filter - Where = [SEARCH_MTR]![HEAT/SLAB] Like "*" & [Forms]![SEARCH_MTR]![Search-HS] & "*"

and

Apply Filter - Where = [SEARCH_MTR]![THICK] Like "*" & [Forms]![SEARCH_MTR]![Search-THICK] & "*"


I want to use this form to search / filter the results from the query based on text entries in any of the 3 text boxes. The goal is to use this as a way to find probable matches based on incomplete information. There are three text boxes but in most cases the search would only be conducted on one or two fields at the most so null values in the text field need to be allowed. The matches need to be like matches, an entry of w7p would return W7P54 and 8W7PF.


Currently when I enter a value into one of the text fields and click the run search command, I receive 3 prompts Enter Parameter Value Forms!TRACE_QUERY!Text23 "which was the text box name of [Search-MTR] prior to it being changed. I receive this prompt three times no matter which filed I search in and depending on which field I am searching, clicking ok without entering a parameter will return incorrect results 2 out of the 3 times. One of the clicks will correctly filter the results but the if its not the last prompt this dose little good.

I was thinking the Macro was allowing for null entries so I am not sure why the prompts are appearing much less altering the results when clicking ok without entering a value.

Any thoughts on what is going wrong or how to fix it will be greatly appreciated. Thanks again everyone, yall have already been a lot help on this

 

[ranman256]

the code looks at the controls, if null, ignore,
if not, apply.

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 


sWhere = "1=1"
if not IsNUll(cboDr) then sWhere = sWhere & " and [phsician]='" & cboDr & "'"
if not IsNUll(cboCity ) then sWhere = sWhere & " and [City]='" & cboCity & "'"


If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

 

[TDC21]

My familiarity with the code side is pretty low, would there be a way to write that into the apply filter macro? Is there a do not apply filter argument, or an all character argument so anything would be considered a match even null values?

something like,

Where Condition= iif(IsNull(Search-MTR), "DO NOT APPLY FILTER", [SEARCH_MTR]![MTR] Like "*" & [Forms]![SEARCH_MTR]![Search-MTR] & "*")


Where Condition= iif(IsNull(Search-HS), "DO NOT APPLY FILTER", [SEARCH_MTR]![HEAT/SLAB] Like "*" & [Forms]![SEARCH_MTR]![Search-HS] & "*")


Where Condition= iif(IsNull(Search-THICK), "DO NOT APPLY FILTER", [SEARCH_MTR]![THICK] Like "*" & [Forms]![SEARCH_MTR]![Search-THICK] & "*")


Some records in the associated query are null so using another filter statement for the true part where there is not a match would filter out results that need to be visible.


Thanks