Hi All,
This is a little long winded but this problem has had me stumped for weeks!
I have a problem concerning multiple criteria in a query. I have a search form that has 3 unbound text boxes. The form also has a subform that is tied to a query. Each text box on the form is tied to a specific field in the query. (e.g textbox1 searches in the strName field, textbox2 searches in the strCompany field, etc.) When a user enters some text in the text box, they then click a button that requeries the subform displaying the records where the text is found in the corresponding field. If only one text box has search criteria entered then the search is performed only on that field, if two text boxes have criteria entered then an “And” search is performed on both fields. I am using the following in the criteria field in my query:
Like “*|Forms![frmDataSearch]![strCriteria1]|*” & “*”
I place a statement similar to this in all of the three fields that I wish to search on. Here is the problem, all 3 fields in the underlying table must have data in them in order for all records to appear. If just one of the fields is null (or “”), the entire record is filtered out of the query. Here is an example:
Record in table:
strName: Bob
strCompany: “”
strRegion: North America
Text box craters
textbox1(search in Name): bob
textbox2(search in Company):
textbox3(search in Region):
This actually returns no records because the strCompany field in the underlying table is empty. What I basically want is that if no criteria is specified in any textbox then show all records, even those where the search fields may be empty strings. If only one of the textboxes has criteria specified, then show all records where that field is similar to the criteria, including those where either of the other two fields may contain empty strings, an so on…
I have tried adding to the search criteria in the query an “Or” condition, but this doesn’t work:
Criteria in query: Like “*|Forms![frmDataSearch]![strCriteria1]|*” & “*” Or “*”
I have tried creating a function to assess the value of the textbox and return one of two possible criteria strings but this also doesn’t work:
Criteria in query: Criteria(Forms![frmDataSearch]![strCriteria1])
It seems that no matter what I put in the criteria of the query, as soon as there is a criteria expression specified, it automatically filters out any records where this field is null or an empty string.
Any ideas?
Itchy
This is a little long winded but this problem has had me stumped for weeks!
I have a problem concerning multiple criteria in a query. I have a search form that has 3 unbound text boxes. The form also has a subform that is tied to a query. Each text box on the form is tied to a specific field in the query. (e.g textbox1 searches in the strName field, textbox2 searches in the strCompany field, etc.) When a user enters some text in the text box, they then click a button that requeries the subform displaying the records where the text is found in the corresponding field. If only one text box has search criteria entered then the search is performed only on that field, if two text boxes have criteria entered then an “And” search is performed on both fields. I am using the following in the criteria field in my query:
Like “*|Forms![frmDataSearch]![strCriteria1]|*” & “*”
I place a statement similar to this in all of the three fields that I wish to search on. Here is the problem, all 3 fields in the underlying table must have data in them in order for all records to appear. If just one of the fields is null (or “”), the entire record is filtered out of the query. Here is an example:
Record in table:
strName: Bob
strCompany: “”
strRegion: North America
Text box craters
textbox1(search in Name): bob
textbox2(search in Company):
textbox3(search in Region):
This actually returns no records because the strCompany field in the underlying table is empty. What I basically want is that if no criteria is specified in any textbox then show all records, even those where the search fields may be empty strings. If only one of the textboxes has criteria specified, then show all records where that field is similar to the criteria, including those where either of the other two fields may contain empty strings, an so on…
I have tried adding to the search criteria in the query an “Or” condition, but this doesn’t work:
Criteria in query: Like “*|Forms![frmDataSearch]![strCriteria1]|*” & “*” Or “*”
I have tried creating a function to assess the value of the textbox and return one of two possible criteria strings but this also doesn’t work:
Criteria in query: Criteria(Forms![frmDataSearch]![strCriteria1])
It seems that no matter what I put in the criteria of the query, as soon as there is a criteria expression specified, it automatically filters out any records where this field is null or an empty string.
Any ideas?
Itchy
