Filter Not Working

[data808]

I have a form I will call FORM1 with a bunch of unbound text boxes. The control source is to a query that has this expression in the criteria row for certain fields:

Like "*" & [Forms]![FORM1].[txtPermit] & "*"
Like "*" & [Forms]![FORM1].[txtName] & "*"
Like "*" & [Forms]![FORM1].[txtDate] & "*"

In the unbound text boxes whatever I type will give me a filter that has a wild card at the beginning or end of the data I enter. After I finish typing this data, I have a filter button that will take me to the next form that I will call FORM2. FORM2 is also set to the same query as FORM1. FORM2 is a split form that will filter out data based on what I typed in FORM1. However, the problem I am having is that the filter does not show null fields for the unbound text boxes from FORM1. Example:

FORM1 has these unbound text boxes:

txtPermit
txtName
txtDate

If I type "John" for the txtName field and click the filter button to open, it will show all the records that have the name John in it except for the records that are null for the txtPermit or txtDate. I would still want these records to show up regardless if they are null or not. As long as "John" is in the record, I want it to show no matter what. Do I have to change the expression in the query for that to happen?

[ranman256]

Test all controls for a possible filter, if not used, skip it...

If not isnull(cboState) then  sWhere = sWhere & " and [state] like '*" & cboState & "*'"
If not IsNull(txtName) then   sWhere = sWhere & " and [Name] like '*" & txtName & "*'"
If not IsNull(txtContact) then  sWhere = sWhere & " and [Contact]  like '*" & txtContact.value & "*'"

'remove 1st And
sWhere= mid(sWhere,4)

sSql = "SELECT * FROM tblArchitectureFirms WHERE "
sSql = sSql & sWhere

[data808]

Don't really understand your code. Right now the only records that show in FORM2 have to have all 3 of the fields filled out. (txtPermit, txtName, and txtDate) I do have a total of 10 fields but because I do not have an expression such as this:
Like "*" & [Forms]![FORM1].[txtPermit] & "*" for the other 7 fields in the query, these 7 fields do not matter if they have data entered into them or not. They will show up in with the filter on. Its just the 3 fields with the expressions in the query and also the ones that have the unbound textboxes in FORM1 that have to have data entered in order for the filter to show these records. I'm thinking the expression is looking for something to be entered in these 3 fields and if nothing is there and the field is null, it skips these records and moves on.

Can anyone help?