Access 2000 How To’s: Building a Query Command Function

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