Void out parameter of SQL query if empty cell selection from Excel - VBA

Question

Instead of using Nested If statements, I was wondering if there is a way to void out parts of a string query if cell value is left blank.

Cell structure is as below:

Cell values from these parameters will get passed into vba code that queries a database. Ideally I don't want to create an individual query for each selection type - and I have it dynamically querying from location already. I want to extend the query to include possible combinations of start, end, value >, value <, while also making it so that if cell value is left blank, then ignore that parameter. So say

SELECT * 
from database 
WHERE location = 'cell_loc' 
AND Value >= 'cell_value' 
AND Value <= 'cell_value' 
AND Start >= 'cell_date' 
AND End <= 'cell_date'

Now imagine that Start is left blank, meaning I want to query from first data point in the database:

I could write a nested if to handle this, but was wondering if there was a way to void out a query parameter so that I could just have a single query fed to database with different parameters changing based off cell data?

Something along the lines of:

SELECT * 
from database 
WHERE location = 'cell_loc' 
AND Value >= 'cell_value' 
AND Value <= 'cell_value' 
AND Start >= 'cell_date' --> this would be voided out
AND End <= 'cell_date'

Take a look at the coalesce() function. https://stackoverflow.com/questions/17104329/how-to-use-coalesce-in-mysql — Sam M, Apr 22 '21 at 15:37
Typically you'd do this by building up the SQL, adding each where clause as needed, based on your query cells being populated or not. — Tim Williams, Apr 22 '21 at 15:41
@SamM hm, it looks like that only returns the first null value from the list. I still want to return the entire selection if the cell value is populated. To make it more clear, the 'cell values' are my query parameters, not the database values I am querying from. — Zachary Wyman, Apr 22 '21 at 15:47
@TimWilliams Do you have an example for just one query cell? I don't need them for all, can replicate but say if you wanted to add that condition to the Start parameter in the query above. — Zachary Wyman, Apr 22 '21 at 15:49

score 2 · Answer 1 · answered Apr 22 '21 at 15:58

2

Using the coalesce() function you can put an equality condition in your WHERE clause. This is a common SQL trick to deal with null parameters or null values in the data.

SELECT * 
from database 
WHERE location = 'cell_loc' 
AND Value >= 'cell_value' 
AND Value <= 'cell_value' 
AND (Start >= 'cell_date' OR Start = coalesce('cell date', Start))
AND End <= 'cell_date'

answered Apr 22 '21 at 15:58

Sam M

4,136
4
29
42

This looks like exactly what I'm looking for. I tested it out - and when there is a value it works just fine! However, if the excel value is left blank I get an incorrect syntax error. When cell is left blank, using Debug.Print query will return ```SELECT ZONE, DATE, GASPRICE FROM ERCOT.GAS WHERE ZONE='NGPL-TxOk' AND (GASPRICE >= OR GASPRICE = coalesce(, GASPRICE))``` which I can see having an error since value is left blank. – Zachary Wyman Apr 22 '21 at 16:28
Just to give you more information to work off of. If I include a value, query will look like ```SELECT ZONE, DATE, GASPRICE FROM ERCOT.GAS WHERE ZONE='NGPL-TxOk' AND (GASPRICE >=4.8 OR GASPRICE = coalesce(4.8, GASPRICE))``` which clearly works since a value is included. Can coalesce still be used to bypass this? – Zachary Wyman Apr 22 '21 at 16:38

Tim Williams · Accepted Answer · 2021-04-22T16:11:20.167

1

Here's a very basic example:

Sub Tester()
    Dim sWhere As String, sql As String
    
    sql = "Select * from myTable "
    
    sWhere = ""
    BuildWhere sWhere, "id = <v>", Range("A5")
    BuildWhere sWhere, "pName = '<v>'", Range("B5")
    BuildWhere sWhere, "pDate > '<v>'", Range("C5")
    
    If Len(sWhere) > 0 Then
        sql = sql & " where " & sWhere
        Debug.Print sql
        'run query
    Else
        'don't run if no criteria ?
    End If

End Sub

'add a where clause only if `c` has a value
Sub BuildWhere(ByRef sWhere As String, test As String, c As Range)
    Dim v
    v = Trim(c.Value)
    If Len(v) > 0 Then
        If Len(sWhere) > 0 Then sWhere = sWhere & vbLf & " and "
        sWhere = sWhere & Replace(test, "<v>", v)
    End If
End Sub

edited Apr 22 '21 at 16:11

answered Apr 22 '21 at 16:04

Tim Williams

154,628
8
97
125

Is this just determining if c has a value using length? Nifty, I use this trick in Python a bit - but am somewhat unfamiliar with VBA. – Zachary Wyman Apr 22 '21 at 16:31
Yes, just checking length to see if there's anything there. And always worth throwing in `Trim()` when dealing with user input. – Tim Williams Apr 22 '21 at 17:01

Void out parameter of SQL query if empty cell selection from Excel - VBA

2 Answers2