Retrieving all records when integer field in combo box is null

[zionman4]

I currently have a form with several combo boxes where some of the fields are strings (text) and others integers. I am creating a query with criteria based on the combo boxes; however, I am having problems retrieving data when the integer fields are left blank.
Here is the code and thanks in advance!!

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim strSQL As String
Dim stDate As String
Dim intSpecialist As Integer

Set dbs = CurrentDb
stDocName = "qProviders"
Set qdf = dbs.QueryDefs(stDocName)

If IsNull(Me.From.Value) Then
stDate = " Like '*' "
Else
stDate = " Between '" & Me.From.Value & "' And '" & Me.To.Value & "' "
End If
If Me.cboRep.Column(2) = -1 Then
intSpecialist = " Like Null "
Else
intSpecialist = "=" & Me.cboRep & ""
End If

strSQL = "SELECT P.Name, P.ProviderID " & _
"FROM dbo_Providers " & _
" WHERE P.ReceivedDate" & stDate & _
"AND P.RepID" & intSpecialist

qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

 

[PHV]

...
Dim intSpecialist As String
...
stDate = " Is Not Null "
...
intSpecialist = " Is Null "
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zionman4]

If I convert inSpecialist which is an ID number on the combo box into a string a text value, I get a "Data Type mismatch in criteria" error.

 

[zionman4]

Actually it worked as you suggested. The mismatch error came from the stDate. I substituded (') for (#) and that did it.

Thanks!!!