(DQB) Dynamic Query Builder - Dynamically build VBA queries fast (1 Viewer)


Registered User.
Local time
Today, 02:25
Sep 20, 2019
Dynamically building unbound queries in VBA (dynamic meaning on-the-fly) can be a daunting task. A common application of this is building search forms. First, all criteria-related controls (inputs) must be built (i.e. search textbox, some checkboxes, etc.), and then tied to the backend, which must process these inputs and return a query that the user requested.

When search forms are built, the logic in grabbing, validating, and organizing inputs from the controls can make for messy and redundant code.

Examples of this include...
1.) First, verifying the input is actually an input. Is the control blank? Is it NULL?
2.) What does a blank control mean in the context of a search form? Does it mean WHERE ID = "" or does it mean WHERE ID = ALL IDs IN TABLE The latter would be correct.
3.) Special cases where a control value equals something irrelevant to the data itself. For example, a combo box has 3 values ("ALL", "Food", "Ingredient"). Does "ALL" mean WHERE Type = "ALL". Or does it mean WHERE Type = 'Food' or 'Ingredient'?

The attached object is DQB - Dynamic Query Builder. It attempts to make building dynamic queries fast, elegant, and compact. It has been designed with an emphasis on building search forms quickly, but may serve many applications. Its been 2 years and I've been meaning to share the project with the community. I use it heavily and have improved the *many* bugs in it over time.

The object can be described by the following methods and properties:

.SQLSource              Base SQL String to build the query off.
.HAVING                 Required property when .SQLSource is a JOIN query
.DebugString            Debug the criteria stack
.Error                  Test for an error in the object

.ReplaceField()           Replace a field value for all records with an expression
.AddCriteriaString()      Manually add a verbatim query string
.AddDateCriteria()        Build criteria for a start date, or an end date, or both
.AddCriteria()            Add basic criteria, with 4 CriteriaTypes (exact, notequal, *like, *like*)
.AddMultiCriteria()       Add multiple criteria like:  "WHERE field = val1 or val2 or val3..."
.AddCompoundCriteria()    Add compound criteria "If field1 = val or field2 = val, or field2 = val"
.NameField()              Rename the field with an expression (Fieldname AS MyExpression)
.ConcatenateField()       Join two fields together with a separator and rename as an expression

Using the code, we can quickly manipulate a SQL statement to produce a dynamic string that updates on a form.
dim DQB as New DynamicQueryBuilder
dim sql as string
With DQB
    .SQLSource = "SELECT * FROM Foods ORDER BY ID;"
    .AddCriteria Search, "ItemID", clikematch
    .AddDateCriteria "Date_Added", StartVal, EndVal

   sql = .GenerateSQL
End with

mylistbox.rowsource = sql

As the criteria stack grows, it is possible an invalid SQL statement was generated. The object verifies the validity of the SQL statement at every instance of a criteria addition. Should an invalid SQL statement, an internal caught error, or a runtime error be produced, the .Error property is set to TRUE. We can analyze the .DebugString property for debugging....

If DQB.Error = False Then 'no error
    listData.RowSourceType = "Table/Query"
    listData.RowSource = DQB.GenerateSQL
    Debug.Print DQB.DebugString
End If

SQL: FAIL       .AddCompoundCriteria(do, ItemdID,Shortname)  FAILED:  Field not found
SQL: PASS       AddCriteria([ALL], Type)  (IGNORED)
SQL: PASS       AddCriteria(False, IsGeneric)
SQL: PASS       .AddDateCriteria(Date_Added, , )   (IGNORED)
SQL: PASS       AddCriteria(A105, ItemID)
SQL: PASS       .ConcatenateFields(ItemID, ShortName , PRODUCT)

This documentation is limited and the attached example shows most of it's capability, but not all. The example includes code comments that are important for the user to understand.

The 'Operator' arg is not covered in the example, which allows the caller to change the AND/OR operator between statement to develop more complexity. The default operator set in each routine covers the most commonly encountered scenario, but can be modified to fit unique needs.

I have used this with huge JOIN queries and its worked successfully. There are probably other queries where it will not work correctly on. This is why I built debugging into the object, so it can be better understood and troubleshot. I hope you find value in it.

Thanks to all

9.26.21 - Released


  • DQB Demo IronFelix717.accdb
    928 KB · Views: 43

Users who are viewing this thread

Top Bottom