Access 2000 Tabular Form - Building a Query Command Function

				

David Nishimoto
davepamn@relia.net
Article: Building a Query Command Function:
Overview: Suppose you have an Access2000 tabular form,
meaning each row is a record.Suppose you want
to have the ability to input filter criteria from the form
and press a query button for the filter to apply. This
article explains how to create this functionality.
The Filter has four textbox called: txtFirstName,
txtMiddleInitial, txtLastName, and txtSSN. The
datbase 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'"
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