Where is this headed?
Filtering a form?
Opening a report?
Opening a recordset?
The example below shows how to build up a string from whichever boxes have
an entry. You can then apply it as the Filter of a form, open a filtered
report, finish builing the SQL string to OpenRecordset, or whatever else you
wish to do with it.
With this approach, the WHERE clause contains only the boxes that *need* to
be in the string, which is far more efficient than the workarounds where you
type complex criteria under all of the possible fields in your query and
Access has to evaluate them all.
The example is designed so it's easy to add as many as you need. It tacks
and AND onto each one, and then chops the trailing AND off the end of the
string at the end. The example also shows how to handle Text fields, Number
fields (including Currency), and Date fields.
-----------------code starts---------------------
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFindSurname) Then 'text example
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If
If Not IsNull(Me.txtFindAmount) Then 'number example
strWhere = strWhere & "([Amount] = " & _
Me.txtFindAmount & ") AND "
End If
If Not IsNull(Me.txtFindSaleDate) Then 'date example
strWhere = strWhere & "([SaleDate] = #" & _
Format(Me.txtFindSaleDate, "mm\/dd\/yyyy") & "#) AND "
End If
'repeat the above for other text boxes as needed.
'Chop off the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If
'Filter the form:
Me.Filter = strWhere
Me.FilterOn = True
'Or perhaps open a report:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
-----------------code ends---------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
I want to create a form with 5 fields and to be able to select records based
on 3 of the fields as search criteria.I have played with queries and SQL and
can't find anything that works right. Any help would be appreciated.
