Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Dec 1, 2002

Microsoft Access 2000 How To's Series - Page 4

By David Nishimoto

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()
    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
            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

    	Occurances = iCount
    End Function

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

Back to Access 2000 How To's Series Home


MS Access Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM

Thanks for your registration, follow us on our social networks to keep up-to-date