Overview
This article explains how to create the ability to input filter criteria from an Access 2000 tabular form (where each row is a record) and press a query button for the filter to be applied.
The filter has four textboxs called txtFirstName, txtMiddleInitial, txtLastName, and txtSSN. The database table "member" has corresponding fields called First, Mi,Last, and SSN.
The AttachAnd function is used to create the SQL filter string: "First='David' and Last='Nishimoto'".
Building the Function
Option Compare Database
Dim ssql
1. User pressed the Query button
2. Or you could catch a textbox on exit event
Private Sub cmdQuery_Click()
BuildQueryCommand
End Sub
1. BuildQueryCommand builds an filter critera by receiving the fieldname and critiera
2. The filter criteria is applied
Private Function BuildQueryCommand()
sSQL = ""
Call AttachAnd("First", "'" & txtFirstName & "'")
Call AttachAnd("Mi", "'" & txtMiddleInitial & "'")
Call AttachAnd("Last", "'" & txtLastName & "'")
Call AttachAnd("SSN", "'" & txtSSN & "'")
Filter = sSQL
FilterOn = True
End Function
1. Check for single or multiple criteria
Single: "first='David'"
Multiple:"First='David' and Last='Nishimoto'"
Private Function AttachAnd(sField, sValue)
If sValue = "''" Or sValue = "" Then
Exit Function
End If
If Occurances(sSQL, "=") = 0 Then
sSQL = sSQL & sField & "=" & sValue
Else
sSQL = sSQL & " and " & sField & "=" & sValue
End If
End Function
1. Count the occurances for a pattern in the SQL phrase.
Private Function Occurances(sSQL, sOperator)
Dim offset
Dim iCount
offset = 1
While offset <> 0
offset = InStr(offset + 1, sSQL, sOperator)
If offset > 1 Then
iCount = iCount + 1
End If
Wend
Occurances = iCount
End Function
Back to Access 2000 How To's Series Home