If/Then in SQL Embedded in VBA

Question

I've got a string like this in my Excel VBA:

strSQL = "SELECT * FROM Total WHERE (SimulationID = (" & TextBox1.Text & ") And Test1 = (" & Example & "))"

However, sometimes Test will be 'is null', which makes the query

And Example = is NULL

How can I change it to add an if/then statement or something to make it say

And Example is null

when Example has a value of "is null"?

Answer 1

I would suggest doing the NULL comparison before assembling the SQL statement strSQL. If you check for the value of Example beforehand, you can alter your strSQL statement accordingly based on that check.

EDIT:
In response to Daniel's first and second comment below, I still would prefer the following over doing it inline:

Dim strSqlTail strSqlTail = ""

If (Example1 = Null) Then strSqlTail = "AND Example1 IS NULL"
If (Example2 = Null) Then strSqlTail = strSqlTail & "AND Example2 IS NULL"
If (Example3 = Null) Then strSqlTail = strSqlTail & "AND Example3 IS NULL"

...

Note: The strSqlTail can be whatever SQL would make your situation work since I don't quite understand what is being queried from the sample.

Answer 2

One solution is to coalesce out the null using a Coalesce function (or if using Access, the Nz function) :

trSQL = "SELECT ..." & _ 
        " FROM Total" & _ 
        " WHERE SimulationID = " & TextBox1.Text & " & _ 
        "   And Coalesce(Test1,"""") = "" & Example & """

A better way would be to dynamically include or not include the entire And statement based on whether Example had a value or to substitute Test Is Null when Example did not have a value. So something akin to:

Dim testElements() As Variant
Dim vElement As Variant
Redim testElements(6,1)

testElements(0,0) = Example1
testElements(0,1) = "Test1"
testElements(1,0) = Example2
testElements(1,1) = "Test2"
...

Dim elementIndex as Integer
Dim colName As String
Dim elementValue As Variant

For elementIndex = 0 To UBound(testElements)
    elementValue = testElement(elementIndex, 0)
    colName = testElement(elementIndex, 1)

    If Len(Nz(elementValue)) = 0 Then
        trSQL = trSQL & " And " & colName & " = """ & Example1 & """
    Else
        trSQL = trSQL & " And " & colName & " Is Null"
    End If
Next

Answer 3

You just create a function that puts the equal sign and space before the number if it doesn't equal "is null", and modify the string assignment statement appropriately, like so:

Public Function NullModString(Example As String) As String

  If Example <> "is null" Then Example = "= " & Example
  NullModString = Example

End Function


strSQL = "SELECT * FROM Total WHERE SimulationID = " & TextBox1.Text & _
         "And Test1 " & NullModString(Example)

Note, that I didn't understand why the extra parentheses were in there, but it would be simple to put them back in.

Answer 4

First, don't embed SQL in VBA: hard to maintain, SQL injection, etc. Use a stored proc on the database side (even Access has PROCEDUREs).

Second, use COALESCE (or equivalent logic) to handle your 'empty string equates to NULL' UI logic. You don't say which SQL syntax and you haven't posted schema DLL so we're merely guessing...

SQL Server:

CREATE PROCEDURE GetTotals
@SimulationID INTEGER, 
@Test1 VARCHAR(20) = NULL
AS
SELECT * 
  FROM Total AS T1.
 WHERE T1.SimulationID = @SimulationID
       AND COALESCE(T1.Test1, '') = COALESCE(@Test1, '');

Access Database Engine (a.k.a. Jet):

CREATE PROCEDURE GetTotals
(
 :SimulationID INTEGER, 
 :Test1 VARCHAR(20) = NULL
)
AS
SELECT * 
  FROM Total AS T1
 WHERE T1.SimulationID = :SimulationID
       AND IIF(T1.Test1 IS NULL, '', T1.Test1) 
          = IIF(:Test1 IS NULL, '', :Test1);

Then execute the proc using your middleware (ADO, DAO, etc) with Parameter objects, using the empty string (or other 'magic' value) for NULL.