• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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.
0
kerrybenno
Asked:
kerrybenno
  • 2
1 Solution
 
cmhuntyCommented:
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 SundaramCommented:
Try replacing as this

If Sector = "Show All" Then
    Sector = "% "  and " Sector is not null "
End If
0
 
Shanmuga SundaramCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now