dcsimg

Microsoft Access 2000 How To's Series - Page 4

December 1, 2002


Building a Query Command Function

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


Next Access "How To": Creating Menus Using Access 2000

Back to Access 2000 How To's Series Home

 








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers