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 SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
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()
        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

 



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