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. stackoverflow.com/questions/17104329/… — Sam M
– Sam M, Commented Apr 22, 2021 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
– Tim Williams, Commented Apr 22, 2021 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
– Zachary Wyman, Commented Apr 22, 2021 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
– Zachary Wyman, Commented Apr 22, 2021 at 15:49

Sam M · Accepted Answer · 2021-04-22 15:58:40Z

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, 2021 at 15:58

Sam M

4,1564 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zachary Wyman Over a year ago

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 Over a year ago

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?

Tim Williams · Accepted Answer · 2021-04-22 16:11:20Z

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, 2021 at 16:11

answered Apr 22, 2021 at 16:04

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

2 Comments

Zachary Wyman Over a year ago

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.

Tim Williams Over a year ago

Yes, just checking length to see if there's anything there. And always worth throwing in Trim() when dealing with user input.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related