Beyond Excel: VBA and Database Manipulation

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

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

Processing your reply...

There was an error processing your information. Please try again later.

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

Processing your reply...

About This Blog

A blog for developers eager to go beyond paper reports into interactive analytical tools and database applications using tools their customers already own and love – Excel and ODBC.