Searching Issues

  • Thread starter Thread starter C. Wall
  • Start date Start date
C

C. Wall

I am trying to create some form of search function for a
single table. I need to be able to search using either a
single field or multiple fields (i.e. city, state, zip,
company code). Is this something I need to code and if
so, can anyone direct me to a good resource? I'm going
through the help library that comes with Access without
much success. Any help would be appreciated!
 
The basic idea is to build up a string to use as the Filter property of the
form. It has to look like the WHERE clause of a query. You can mock up a
query with some dummy values for Criteria, and then switch it to SQL View
(View menu in query design) to see what the WHERE clause looks like.

To build the string, visit each of the text boxes and include them only if
they have a value. You need " AND " between the different phrases, so we
tack that on to each one, and chop off the last one at the end.

Private Sub cmdApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFindCity) Then
strWhere = strWhere & "([City] = """ & Me.City & """) AND "
End If
If Not IsNull(Me.txtFindState) Then
strWhere = strWhere & "([State] = """ & Me.State & """) AND "
End If
'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

Note: Omit the extra quote marks for fields of type Number.
For Date fields, use # as the delimiter.
 
Back
Top