It’s Time to Play

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)