VBA - create a condition based SQL query

1 answer

I'm quite new to VBA and I would like to find a simpler way to create a SQL query. I have 4 comboboxes and the query depends on if they are filled or not. One of the combobox is linked to a userform with multiple checkboxes that would add to the query.

Here is the code I have at the moment and I want to increment the userform conditions:

If Sheets("Home Page").opecbbx.Value <> "" And Sheets("Home Page").wellchk.Value = False Then 'contractor only         op = Sheets("Home Page").opecbbx.Value                  If Sheets("Home Page").sizcbbx.Value <> "" Then 'contractor and size                     'Create SQL statement for filtering.                     siz = Sheets("Home Page").sizcbbx.Value                     SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%' And size_sec Like '%" & siz & "%';"                  Else                      SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%';"                  End If      ElseIf Sheets("Home Page").wellchk.Value = True And Sheets("Home Page").opecbbx.Value <> "" Then 'contractor and well     op = Sheets("Home Page").opecbbx.Value                  If Sheets("Home Page").sizcbbx.Value <> "" Then 'contractor, size and well                              siz = Sheets("Home Page").sizcbbx.Value                             SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%' And size_sec Like '%" & siz & "%' And ID_NOC = '" & well & "';"                  Else                      SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%' and ID_NOC='" & well & "';"                  End If      ElseIf Sheets("Home Page").wellchk.Value = True Then                  If Sheets("Home Page").sizcbbx.Value <> "" Then 'size and well                      siz = Sheets("Home Page").sizcbbx.Value                     SQL = "SELECT * FROM Filter Where size_sec Like '%" & siz & "%' And ID_NOC = '" & well & "';"                  Else                       SQL = "SELECT * FROM Filter Where ID_NOC='" & well & "';"                  End If      ElseIf Sheets("Home Page").sizcbbx.Value <> "" And Sheets("Home Page").wellchk.Value = False And Sheets("Home Page").opecbbx.Value = "" Then 'size only                  siz = Sheets("Home Page").sizcbbx.Value                 SQL = "SELECT * FROM Filter Where size_sec Like '%" & siz & "%';"      'Formation only     ElseIf Sheets("Home Page").wellchk.Value = False And Sheets("Home Page").opecbbx.Value = "" And Sheets("Home Page").sizcbbx.Value = "" Then                  SQL = "SELECT * FROM Filter"      End If 

How can I do that without adding lots of new conditions?

Thanks

All answers to this question, which has the identifier 60567419

The best answer:

I can give a general answer here which might require some massaging to get working in VBA. You may use a flexible prepared statement like this:

SELECT * FROM Filter WHERE     (contractor LIKE ? OR contractor IS NULL) AND     (size_sec LIKE ? OR size_sec IS NULL) AND     (ID_NOC = ? OR ID_NOC IS NULL); 

To each of the placeholders ?, you would either bind the value from the combobox, if defined, or just bind NULL otherwise. For example, for the contractor placeholder, you would bind op if defined, otherwise NULL. The way this approach works is that if a given criteria be NULL, then it gets ignored. So, if you only had one of the three values available from the combobox, then only it would be used to filter in the WHERE clause.

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type com.google.firebase.iid.zzav is referenced as an interface from com.google.firebase.messaging.zzd”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data