Beyond Excel: VBA and Database Manipulation

Dec 10 2009   6:03PM GMT

It’s Time to Play



Posted by: Craig Hatmaker
Tags:
database
development
excel
Microsoft Excel
ms query
odbc
sql
tutorial
vba

In the last post, we integrated frmPrompt into Macro1(), but we didn’t take full advantage of what frmPrompt offers.  We limited values to just one for each parameter.  Today we are going to add the ability to filter our database using wildcards or lists.

We will be adding a new function called Build_SQL_ID.  Build_SQL_ID looks at the value from frmPrompt and determines if our user intended to:

  • Filter at all
  • Filter on just one value
  • Filter on a list
  • Filter based on a wildcarded value

I hope you can see the power this gives our customers to mine information from their data.

We will be changing the SQL statement slightly to let Build_SQL_ID add the appropriate comparison.  We’ll look at Build_SQL_ID first, then be sure to check the changed SQL statement at the end.  Add Build_SQL_ID to your module and replace the SQL statement in Macro1() with this new one.

After completing this, click your easy button to bring up frmPrompt, key in different values for customers or products, select multiple values from lists, use wildcards, or leave the fields blank alltogether.  See how it changes your pivot table and chart.  It’s time to play.  Enjoy.

Public Function Build_SQL_ID(sField As String, sValue As String, bAddQuotes As Boolean) As String
'   Build_SQL_ID: Create a field's comparison string
'                 If sValue = "*ALL" then no comparison string is desired
'                 If sValue contains a wild card then "LIKE" must be used
'                 Otherwise "IN" must be used
'   Parameters:   sField     Table's column name
'                 sValue     The value to filter results on
'                 bAddQuotes Set to True for character values
'   Example:      sSQL = "Select * " & _
'                        "From   Employees " & _
'                        "Where  Status = 'ACTIVE' " & _
'                         Build_SQL_ID("STATE", "'MN', 'VA', 'ND'")
'     Date   Init Modification
'   11/10/09 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_ID = ""
   
    If Trim(sField) > "" And Trim(sValue) > "" Then
        If bAddQuotes Then sValue = "'" & _
            Replace(Replace(sValue, "'", ""), ",", "','") & "'"
        Build_SQL_ID = _
            IIf(UCase(sValue) = "*ALL", "", _
                IIf(InStr(1, sValue, "?") > 0 Or InStr(1, sValue, "%") > 0, _
                   "  And   " & sField & " like " & sValue & " ", _
                       "  And   " & sField & " in (" & Trim(sValue) & ") " _
                    ) _
                )
    End If
ErrHandler:
  
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_ID- Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

 

New SQL statement

            sSQL = "SELECT  O.`Order ID`, O.`Customer ID`, " & vbCr & _
                   "        O.`Order Date`, C.`First Name`, " & vbCr & _
                   "        O.`Ship State/Province`, D.Quantity, " & vbCr & _
                   "        P.`Product Name` " & vbCr & _
                   "FROM    Customers C, Orders O, " & vbCr & _
                   "        `Order Details` D, Products P " & vbCr & _
                   "WHERE   O.`Customer ID` = C.ID " & vbCr & _
                   "  AND   O.`Order ID`    = D.`Order ID` " & vbCr & _
                   "  AND   D.`Product ID`  = P.ID " & vbCr & _
                   "  AND   O.`Order Date` Between #" & _
                            Format(.pFrom, "mm/dd/yyyy") & "# And #" & _
                            Format(.pTo, "mm/dd/yyyy") & "# " & vbCr & _
                    Build_SQL_ID("O.`Customer ID`", .pID1, False) & vbCr & _
                    Build_SQL_ID("P.`Product Code`", .pID2, True)
 

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: