Help! Multiple combo box IF THEN to filter on NULL

[CTjunior]

I'm going to start off by saying I have no clue what any of the code I post means (I'm a complete newb when it comes to VBA). I'm learning using the "trial by fire" method. I found the code I'm using in this forum and it does part of what I need it to do.

Here's the problem. I have a main form with multiple combo boxes that filter a subform datasheet. In my main form combo box [PR_Filter] I added a selection titled "<Blanks>". When I select "<Blanks>", I want it to filter my subform field [PR] for NULL values. If I select anything else I want it to filter on that selection. I cannot get the NULL filter to work. Any help is greatly appreciated!

Here is the code that I have (Red is the field I need the NULL values):

Private Function PurchaseFilter()
 
    Dim strFilter       As String
    Dim bFilter         As Boolean
 
 
bFilter = False
strFilter = ""
 
If Nz(Me.ID_Filter, "") > "" Then   'ID
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "ID = '" & Me.ID_Filter & "'"
    bFilter = True
    End If
 
If Nz(Me.Item_Filter, "") > "" Then    'Item
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "Item = '" & Me.Item_Filter & "'"
    bFilter = True
    End If
 
If Nz(Me.WO_Filter, "") > "" Then    'WorkOrder
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "WorkOrder = " & Me.WO_Filter & ""
    bFilter = True
    End If
 
If Nz(Me.PO_Filter, "") > "" Then    'Puchase Order
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "PO = " & Me.PO_Filter & ""
    bFilter = True
    End If
 
[COLOR=red]If Nz(Me.PR_Filter, "") > "" Then 'Puchase Request[/COLOR]
[COLOR=red]If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "[/COLOR]
[COLOR=red]strFilter = strFilter & "PR = " & Me.PR_Filter & ""[/COLOR]
[COLOR=red]bFilter = True[/COLOR]
[COLOR=red]End If[/COLOR]
 
 
If bFilter Then
    Me.PU_Purchasing_Update_sub.Form.OrderBy = ""
    Me.PU_Purchasing_Update_sub.Form.Filter = strFilter
    Me.PU_Purchasing_Update_sub.Form.FilterOn = True
    Else
    Me.PU_Purchasing_Update_sub.Form.FilterOn = False
    End If
End Function

 

[WayneRyan]

CT,

I'm not sure if the filter wants the Access "IsNull(PR)" or the SQL "PR Is Null".

Try:

If Nz(Me.PR_Filter, "") > "" Then 'Puchase Request
   If Len(strFilter) = 0 Then strFilter = strFilter & " And "
   If Me.PR_Filter = "<Blanks>" Then
      strFilter = strFilter & "IsNull(PR)"
   Else
      strFilter = strFilter & "PR = " & Me.PR_Filter
   End If
   bFilter = True
End If

Wayne

 

[CTjunior]

Wayne,

Thanks for the response! I tried your suggestion. I'm getting this error now:

The expression After Update you entered as the event property setting produced the following error: Syntax error (missing operator) in query expression ' And PR = <Blank>'.

Would this be because Access is looking for a number first and when it tries to find "<Blank>" it doesn't know what to do with text? How would I fix this?

 

[pr2-eugin]

What is the CODE you currently have in place?

 

[CTjunior]

PR2 here is what I currently have:

If Nz(Me.PR_Filter, "") > "" Then 'Puchase Request
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    If Me.PR_Filter = "<Blanks>" Then
      strFilter = strFilter & "IsNull(PR)"
   Else
      strFilter = strFilter & "PR = " & Me.PR_Filter
   End If
   bFilter = True
End If

This is just for the "PR" filter. For the full code, please see my first post.

 

[pr2-eugin]

So technically your CODE is getting mixed up somewhere. Try DEBUGGING the code using the immediate window.. Print each criteria once it is generated, you will see the problem with your CRITERIA generation.

 

[CTjunior]

I tried the debug.print and I don't know how to make that work. I am completely green when it comes to VBA, as in I honestly don't know what any of the language means. I don't know how to type strings. Nothing. The only CODE I have ever used is stuff I have searched for that does what I need it to do. Copy & Paste is a beautiful thing, but only takes you so far.

Again, I really appreciate you patience and help.

 

[pr2-eugin]

Forget about using Debug.Print.. Try MsgBox..

Private Function PurchaseFilter()
    Dim strFilter As String
    Dim bFilter As Boolean
    bFilter = False
    strFilter = ""
    
    [COLOR=Green]'ID[/COLOR]
    If Nz(Me.ID_Filter, "") <> "" Then                       
        If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
        strFilter = strFilter & "ID = '" & Me.ID_Filter & "'"
        bFilter = True
    End If

   [COLOR=Red] MsgBox strFilter[/COLOR]
    
    [COLOR=Green]'Item[/COLOR]
    If Nz(Me.Item_Filter, "") <> "" Then                                    
        If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
        strFilter = strFilter & "Item = '" & Me.Item_Filter & "'"
        bFilter = True
    End If
    
    [COLOR=Red]MsgBox strFilter[/COLOR]
    
    [COLOR=Green]'WorkOrder[/COLOR]
    If Nz(Me.WO_Filter, "") <> "" Then                                        
        If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
        strFilter = strFilter & "WorkOrder = " & Me.WO_Filter & ""
        bFilter = True
    End If

    [COLOR=Red]MsgBox strFilter[/COLOR]
    
    [COLOR=Green]'Puchase Order[/COLOR]
    If Nz(Me.PO_Filter, "") <> "" Then    
        If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
        strFilter = strFilter & "PO = " & Me.PO_Filter & ""
        bFilter = True
    End If
    
    [COLOR=Red]MsgBox strFilter[/COLOR]
    
   [COLOR=Green] 'Puchase Request[/COLOR]
    If Nz(Me.PR_Filter, "") <> "" Then 
        If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
        
        If Me.PR_Filter = "<Blanks>" Then
            strFilter = strFilter & "IsNull(PR)"
        Else
            strFilter = strFilter & "PR = " & Me.PR_Filter
        End If
        
        bFilter = True
    End If
    
    [COLOR=Red]MsgBox strFilter[/COLOR]
    Debug.Print strFilter

    If bFilter Then
        Me.PU_Purchasing_Update_sub.Form.OrderBy = ""
        Me.PU_Purchasing_Update_sub.Form.Filter = strFilter
        Me.PU_Purchasing_Update_sub.Form.FilterOn = True
    Else
        Me.PU_Purchasing_Update_sub.Form.FilterOn = False
    End If
End Function

See if you get the right Filter generated.. See what you get at the last value.. To access the Immediate window, go to the VBA Editor (Alt+F11) and then CTRL+G to show the immediate window.. Copy and Paste what you get there..

 

[CTjunior]

This is all I got in the Immediate Window:

PR = <Blank>

 

[pr2-eugin]

So you have not selected <BLANKS> from the ComboBox have you? You are just Clicking the button? Without selecting anything?

 

[CTjunior]

I am selecting <Blanks>. That is a selection I added using a UNION Query. I attached a copy of the database. The CODE I am having trouble with is a private function.

 

[pr2-eugin]

Would appreciate if you could Convert the Linked tables to Local tables, and try uploading the File again.

Help us to help you !

 

[CTjunior]

Sorry about that. I was in a rush going to a meeting. Here it is.

 

[pr2-eugin]

In the UNION Query you have used "<Blank>" and in the CODE you have used "<Blanks>" to check in the IF condition.. Change either of the ones, you should get the required Result..

Also change your RowSource for the PR ComboBox as..

SELECT Nz(PR,"<Blank>") AS newPR FROM Materials GROUP BY Nz(PR,"<Blank>") ORDER BY Nz(PR,"<Blank>");

 

[CTjunior]

It Worked! You don't know how much I appreciate your time and patience. You have been a blessing!

 

[pr2-eugin]

Glad to have helped. Good Luck !