CODEWITHSUNDEEP
ms-accesscomboboxvba
SQLnRprobs
SQLnRprobs

Reputation: 77

Invalid Use of Null in Access ComboBox

I am trying to use a ComboBox to append a query through a form I built. The Combobox should be optional, but I can't seem to get around the Invalid use of null error. Here is the code I have currently

Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim Box1 As String
Dim strBox1 As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()

'General SQL Code
strSQL = "SELECT * FROM Test1"

'Build the IN string by looping through the listbox
For i = 0 To List6.ListCount - 1
    If List6.Selected(i) Then
        If List6.Column(0, i) = "_All" Then
            flgSelectAll = True
        End If
        strIN = strIN & "'" & List6.Column(0, i) & "',"
    End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Test1.Brand_Name] in " & _
           "(" & Left(strIN, Len(strIN) - 1) & ")"
'Create the AND string
Box1 = Me.Combo8.Value
 If IsNull(Me.Combo8.Value) Then
 strBox1 = Nz(Me.Combo8.Column(0), "")
 Else: strBox1 = " AND [Test1.Population] = '" & Box1 & "'"
 End If
If Not flgSelectAll Then
    strSQL = strSQL & strWhere & strBox1
End If
 MyDB.QueryDefs.Delete "cpwg"
Set qdef = MyDB.CreateQueryDef("cpwg", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "cpwg", acViewNormal

I have also tried

If IsNull(Box1) Or Box1 = "Null" Then
 strBox1 = Nz(Me.Combo8.Column(0), "")
Else: strBox1 = " AND [Test1.Population] = '" & Box1 & "'"
 End If

Upvotes: 1

Views: 888

Answers (2)

Doug Coats
Doug Coats

Reputation: 7107

IF IsNull(Trim(me.combo8)) Then
    'Do Stuff
End if

Upvotes: 1

scott
scott

Reputation: 153

Try: 

if isnull(me.combo8) then

Also, I Dont know how your combobox is populated, But Null is different than no data. Perhaps try

if me.combo8.value = "" then

Upvotes: 2

Related Questions