SQL retrieving records where a field isNull

In Excel I am retrieving data in vba using sql.

I have the following code;


Function SummarySQL()

Dim FirstDate As Date
Dim Sector As String
Dim Team As String
Dim TabCode As String

FirstDate = Range("FirstDate").Value
Sector = Range("Sector").Value
Team = Range("Team").Value
TabCode = "Additional Resources"

If Sector = "Show All" Then
    Sector = "%"
End If

If Team = "Show All" Then
    Team = "%"
End If

SummarySQL = "SELECT * FROM [" & DataRange & "] WHERE [Month Year] = " & CDbl(FirstDate) & " " & _
             "AND [Sector] Like '" & Sector & "'" & " " & _
             "AND [TabCode] Like '" & TabCode & "'" & " " & _
             "AND [Team] Like '" & Team & "'" & ";"

End Function


Sometimes the Sector field will have been left empty, so where it's returning all with %, I also need it to return the nulls too.  When Sector <> % I don't want the nulls.

i hope this makes sense.

Thanks in advance.
kerrybennoAsked:
Who is Participating?
 
cmhuntyConnect With a Mentor Commented:
Try something like.........

SummarySQL = "SELECT * FROM [" & DataRange & "] WHERE [Month Year] = " & CDbl(FirstDate) & " " & _
             "AND ([Sector] Like '" & Sector & "'" & " "
If Sector = "" Then
   SummarySQL += " OR Sector IS NULL"
End If

SummarySQL += ") AND [TabCode] Like '" & TabCode & "'" & " " & _
             "AND [Team] Like '" & Team & "'" & ";"

Notice the additional brackets round the sector part of the WHERE clause.

The other option would be to do the work in a stored procedure. This would be less prone to errors.
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
Try replacing as this

If Sector = "Show All" Then
    Sector = "% "  and " Sector is not null "
End If
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
Sorry I am wrong in previous. Try replacing as this
If Sector = "Show All" Then
    Sector =  " [Sector] Like  % "
End If

If Team = "Show All" Then
    Team = "%"
End If

If Sector <> " [Sector] Like  %'"  Then
    Sector = " Sector is not null "
End If


SummarySQL = "SELECT * FROM [" & DataRange & "] WHERE [Month Year] = " & CDbl(FirstDate) & " " & _
             "AND "  & Sector & "  & _
             "AND [TabCode] Like '" & TabCode & "'" & " " & _
             "AND [Team] Like '" & Team & "'" & ";"

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.