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

[ironfelix717]

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
Else
    Debug.Print DQB.DebugString
End If

OBJECT INITIALIZED
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
-IronFelix717

---

---

CHANGE LOG:
9.26.21 - Released

 

[axionvb]

Hi ironfelix717,
I adapted the DQB example to my own database. Queries like "SELECT * FROM TableName ORDER BY ID" are fine, as long as the fields are text or numbers, there is no problem. Well, if the query is set with the search feature in more than one field in the table in the list box, how do I show the text data in the list box instead of the ID of the search field. So the query is "SELECT T1.ID, T1.Date, T2.Dealer, T3.City, T4.Country, T1.Description From T4 RIGHT JOIN (T3 RIGHT JOIN (T2 RIGHT JOIN T1 ON T2.ID = T1.Dealer) ON T3.ID = T1.City) ON T4.ID = T1.Country ORDER BY T1.Country; how should we prepare the class module.

If you examine the attached example, the list on the left is populated with DQB. I want the list on the right to be filled as I want, using DQB. If you notice, there are all the fields in the list on the left, whereas the list on the right has the fields I want to be. I want to use this in a large number of my forms using DQB. I change the table designs from time to time, I don't want to go to the relevant forms and edit the codes each time, I just want to make changes in the function in the relevant form.

Thank you.

 

[ironfelix717]

Hi ironfelix717,
I adapted the DQB example to my own database. Queries like "SELECT * FROM TableName ORDER BY ID" are fine, as long as the fields are text or numbers, there is no problem. Well, if the query is set with the search feature in more than one field in the table in the list box, how do I show the text data in the list box instead of the ID of the search field. So the query is "SELECT T1.ID, T1.Date, T2.Dealer, T3.City, T4.Country, T1.Description From T4 RIGHT JOIN (T3 RIGHT JOIN (T2 RIGHT JOIN T1 ON T2.ID = T1.Dealer) ON T3.ID = T1.City) ON T4.ID = T1.Country ORDER BY T1.Country; how should we prepare the class module.

If you examine the attached example, the list on the left is populated with DQB. I want the list on the right to be filled as I want, using DQB. If you notice, there are all the fields in the list on the left, whereas the list on the right has the fields I want to be. I want to use this in a large number of my forms using DQB. I change the table designs from time to time, I don't want to go to the relevant forms and edit the codes each time, I just want to make changes in the function in the relevant form.

Thank you.

@axionvb
I will look at this for you within next 24 hours. Reply if I don't get to it.

Also this version of DQB is out of date. I haven't passed the update up to the community yet but it has some bugs.

 

[ironfelix717]

@axionvb

I am getting error on your form about ActiveX control.

You need to wipe that form and make a new example. It is corrupt or something. Also you need to use OPTION EXPLICIT and write your code accordingly. Re-build and attach. Code looks fine otherwise.

 

[axionvb]

Tekrar merhaba,
İngilizcem için üzgünüm. Dosyayı yeniden oluşturdum. Dediğiniz düzenlemeyi yaptım. Çözüm önerinizi bekliyorum. Yapabileceklerim üzerinde çalışıyorum. Teşekkür ederim.

Not: Bu bir google translate çevirisidir. İngilizcem çok kötü bu yüzden çeviri olarak yazıyorum.

Google translate of the above message. (jdraw)
Hello again, Sorry for my english. I recreated the file. I made the edit you said. Waiting for your solution suggestion. I'm working on what I can do. Thank you. Note: This is a google translate translation. My English is very bad so I write it as a translation.

 

[axionvb]

Hello,
I found a piece of code like below. This code is returning all the associated table and field names in the database. connect this code to the table we are processing and use in your DQB class and then
strSQL="FROM"
For x=0 .....
strSQL=strSQL & T4 & " RIGHT JOIN".....
next
If we can define the inter-table connection in the form and add it to the remaining SQL string, it will work. The following snippet looks at all the relationships in the database. We'll have to adapt this to the table we're working on. our final SQL string: "SELECT T1.ID, T1.Date, T2.Dealer, T3.City, T4.Country, T1.Description From T4 RIGHT JOIN (T3 RIGHT JOIN (T2 RIGHT JOIN T1 ON T2.ID = T1.Dealer) ) ON T3.ID = T1.City) When ON T4.ID = T1.Country ORDER BY T1.Country; the operation will be completed.

The piece of code I found:
Dim Db As DAO.Database
Dim Rel As Relation

Set Db = CurrentDb

For Each Rel In Db.Relations
With Rel
Debug.Print .Name
Debug.Print .Table & "-" & .Fields(0).Name
Debug.Print .ForeignTable & "-" & .Fields(0).ForeignName
End With
Next
Db.Close

 

[ironfelix717]

Tekrar merhaba,
İngilizcem için üzgünüm. Dosyayı yeniden oluşturdum. Dediğiniz düzenlemeyi yaptım. Çözüm önerinizi bekliyorum. Yapabileceklerim üzerinde çalışıyorum. Teşekkür ederim.

Not: Bu bir google translate çevirisidir. İngilizcem çok kötü bu yüzden çeviri olarak yazıyorum.

Google translate of the above message. (jdraw)
Hello again, Sorry for my english. I recreated the file. I made the edit you said. Waiting for your solution suggestion. I'm working on what I can do. Thank you. Note: This is a google translate translation. My English is very bad so I write it as a translation.

Hi,

You did not understand me. You must delete that form. Corrupt. See attached picture above.

I fixed issue with DQB code that was giving me an issue. Also, Russian (or whatever language you speak) gives unpredictable results on my computer because fields are not named as they appear.

See my code. Get rid of first form. Junk.

 

[axionvb]

Dear ironfelix717,
I rearranged the SQLSource property you wrote in the DQB class with the following codes. It seems to work. But it needs to be simplified a bit and it needs to be tested whether it fits every query shape. piece of code is a bit novice. Because when we find the related field, we get the source table name of this field, the associated table name and the ID field names. However, I could not get the information of which field the text data is instead of the ID numerical data in the related table. so I added some expressions to the "StatusBarText" property of the relevant fields of my source table (the table whose data will be displayed in the Listbox). For example "True|text domain". then I passed this expression through a series of checks inside the function. If it is true it will show in the listbox, if not it will not show it and if it is true it will take the text field name in the related table where the text expression is. This part seems a bit clumsy to me. How does Access set the related field as a text field, not an ID field, in a subform created in tables, for example, in datasheet view. When I solve this issue, I will have solved the inexperience issue I mentioned above.

Note: This is a google translate.

Here is my code block:
Dim Db As DAO.Database
Dim Fld As DAO.Field
Dim rFld As DAO.Field
Dim Rel As DAO.Relation
Dim strDomainNames As String
Dim strBag As String
Dim strBagTableList As String
Dim vBagTableList As Variant
Dim strBagList As String
Dim vBagList As Variant
Dim vText As Variant
Dim strSELECT As String
Dim strFROM As String
Dim strON As String
Dim strORDER As String
Dim v As Integer

Set Db = CurrentDb
Set Rs = Db.OpenRecordset(SQL)

For Each Fld In Rs.Fields
If Fld.ValidationText <> "" Then
vText = Split(Fld.ValidationText, "|")
If vText(1) <> "" Then
If vText(0) = "True" Then
For Each Rel In Db.Relations
If Fld.SourceTable = Rel.ForeignTable Then
For Each rFld In Rel.Fields
If Fld.Name = rFld.ForeignName Then
BagStatus = True
strBagTableList = strBagTableList & Rel.Table & "|"
strBagList = strBagList & Rel.Table & "." &rFld.Name & " = " & Rel.ForeignTable & "." & Fld.Name & "|"
strDomainNames = strDomainNames & Rel.Table & "." & vText(1) & ", "
End If
Next
End If
Next
End If
ElseIf vText(0) = "True" Then
strDomainNames = strDomainNames & Table_Name & "." &Fld.Name & ", "
End If
else
strDomainNames = strDomainNames & Table_Name & "." &Fld.Name & ", "
End If
Next Fld
strDomainNames = Left(strDomainNames, Len(strDomainNames) - 2)
strBagTableList = Left(strBagTableList, Len(strBagTableList) - 1)
strBagList = Left(strBagList, Len(strBagList) - 1)
strSELECT = "SELECT " & strDomainNames
If BagStatus = True Then
strFROM = "FROM"
vBagTableList = Split(strBagTableList, "|")
vBagList = Split(strBagList, "|")
For v = UBound(vBagTableList) To LBound(vBagTableList) Step -1
strFROM = strFROM & vBagTableList(v) & " RIGHT JOIN ("
next v
strFROM = Left(strFROM, Len(strFROM) - 1)
strFROM = strFROM & Table_Name
For v = LBound(vBagList) To UBound(vBagList)
strON = strON & " ON " & vBagList(v) & ")"
next v
strON = Left(strON, Len(strON) - 1)
else
strFROM = " FROM " & SQL_TableName_Getir(SQL)
End If
SQL_Sec_Genislet = strSELECT & strFROM & strON & " " & Get_Sort_Sentence(SQL)

Thank you

 

[axionvb]

Hi ironfelix717,
I did as you said. I created a database from scratch and created a form from scratch and added the codes. It doesn't give me this warning. There are no missing references. I am not getting any compilation errors. You can transfer the codes to the database file you created and examine them. The codes I wrote in the previous message work, but they need to be edited in expert hands. It would be helpful if you could give the final shape to the codes. Also, you wrote in previous messages that DQB is not the final version and you are working on it. Can you share the latest version? Maybe I can do something about correcting mistakes. Thank you.

 

[Pat Hartman]

.HAVING Required property when .SQLSource is a JOIN query

That is an incorrect definition of HAVING. HAVING has nothing to do with a join.

HAVING and WHERE are both clauses that apply selection Criteria except that HAVING is ONLY used in queries that aggregate data. In those queries, you might have both a WHERE and a HAVING or either or neither.

WHERE is criteria that is applied BEFORE the data is aggregated and the column the criteria references does NOT even need to be included in the final recordset so the Show box could be unchecked.

HAVING is criteria that is applied AFTER the data is aggregated and it refers to a field that is aggregated but NOT to any of the Group By fields since those should have been referenced in the WHERE clause.

So, the WHERE clause might be
WHERE City = "Boston"
and the HAVING clause might be
HAVING Sum(OrderTotal) > 5000