Multiple Criteria Search (5+ Search Fields)

[TOMH]

Have seen several posts similar to this, but am still having problems,
possibly because I have so many search fields....

I have created a form with seven blank fields, most of them combo boxes, one
of them a text box. There is a subform set to datasheet view that has a query
as its source.
Each blank field in the main form corresponds to one of the fields of the
query (ex: Area, Unit, Work Order #, Description). I want users to be able to
enter search criteria into any one of the fields or any combination of the
fields to return results, displayed in the subform.
Fields left blank should result in all records for that field,but those
records must still conform to the other search terms NOT left blank.
Problems keep showing up because some of the records in the query contain
Null values in some fields. I need those records to still show up if one of
the other criteria matches that record but not show up if a specific search
term has been entered into the field containing the Null value. (ex: if one
record has a null description, but I search by Unit, I still want that record
to show up. But if I search by description AND unit, I don't want the records
with null descriptions to show up.)
Have tried multiple SQL and VB codes for this and can't get any of them to
work right.

Thanks in advance to anyone who can save my sanity.

 

[Jason]

Thom, try this: In your query, the one used for your subform, create a
sreies of new columns, one for each of your search criteria. Then use
formulaslike this: Unit_x: =IIf([Unit] is null,0,[unit])

Then simply uncheck the "show" boxes for your origional columns. By doing
this you will eliminate all of your null values by replacing them with 0's.
All of filled fields will retain their origional values.
You will then need to go back to your form and make the necessary changes
due to the new field names in your query. And yes you do need to give the
new fields different names than the origional fields. Access will throw you
an error message if you do not.

Hope this helps,

Jason

 

[Duane Hookom]

I would change the record source of the subform based on the criteria. Try
something like:

Dim strSQL as String
strSQL = "SELECT * FROM qselYourQuery WHERE 1=1 "
' building the sql will depend on your control names and data types
If Not IsNull(Me.cboArea) Then
strSQL = strSQL & " And [Area]=""" & Me.cboArea & """ "
End If
If Not IsNull(Me.txtUnit) Then
strSQL = strSQL & " And [Unit] = " & Me.txtUnit & " "
End If
'....more ifs

Me.sfrmYourSubform.Form.RecordSource = strSQL

 

[Allen Browne]

Tom, there is a little example database you can download here if you are
using Access 2000 or above:
http://allenbrowne.com/unlinked/Search2000.zip

It uses the same code as Duane suggested, though it builds just the WHERE
clause and applies it to the form's Filter instead of its RecordSource.

It demonstrates how to build the Filter string for different field types.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would change the record source of the subform based on the criteria. Try
something like:

Dim strSQL as String
strSQL = "SELECT * FROM qselYourQuery WHERE 1=1 "
' building the sql will depend on your control names and data types
If Not IsNull(Me.cboArea) Then
strSQL = strSQL & " And [Area]=""" & Me.cboArea & """ "
End If
If Not IsNull(Me.txtUnit) Then
strSQL = strSQL & " And [Unit] = " & Me.txtUnit & " "
End If
'....more ifs

Me.sfrmYourSubform.Form.RecordSource = strSQL
--
Duane Hookom
MS Access MVP

Have seen several posts similar to this, but am still having problems,
possibly because I have so many search fields....

I have created a form with seven blank fields, most of them combo boxes,
one
of them a text box. There is a subform set to datasheet view that has a
query
as its source.
Each blank field in the main form corresponds to one of the fields of the
query (ex: Area, Unit, Work Order #, Description). I want users to be
able to
enter search criteria into any one of the fields or any combination of
the
fields to return results, displayed in the subform.
Fields left blank should result in all records for that field,but those
records must still conform to the other search terms NOT left blank.
Problems keep showing up because some of the records in the query contain
Null values in some fields. I need those records to still show up if one
of
the other criteria matches that record but not show up if a specific
search
term has been entered into the field containing the Null value. (ex: if
one
record has a null description, but I search by Unit, I still want that
record
to show up. But if I search by description AND unit, I don't want the
records
with null descriptions to show up.)
Have tried multiple SQL and VB codes for this and can't get any of them
to
work right.

Thanks in advance to anyone who can save my sanity.