VBA Case Statement selecting the wrong block

[VSolano]

Hi,

Can someone please check this code to and give me some input to see why it is failing.

I am filtering a form for entity and cocode and the codes is selecting the block for entity, benefits and cocode

Private Sub btnfilterdata_Click()

'On Error GoTo Problem:

Dim FilterData As String
Dim InvYR As Integer
Dim InvAmt As Currency
Dim InvNO As String
Dim InvMonth As Integer
Dim Entity As Integer
Dim CoCode As Integer
Dim Benefits As Integer
Dim RecordCount As Long
Dim SQLCount As String




Entity = Nz(Me.cboentitysearch, 0)
CoCode = Nz(Me.cbococodesearch, 0)
Benefits = Nz(Me.cbobenefitssearch, 0)
InvYR = Nz(Me.txtinvoiceyear)
InvAmt = Nz(Me.txtinvoiceamount)
InvNO = Nz(Me.txtinvoicenumber, 0)
InvMonth = Nz(Me.cboinvoicemonth)
'InvAmt = Nz(Me.txtinvoiceamount)




Dim STRWhere As String

Select Case True

            'filtering by Entity
            
                
        Case Not IsNull(Entity) And (CoCode = 0 And Benefits = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
        
        
                                                     
                      STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & ""
                      
                'filtering by benefits
                      
        Case Not IsNull(Benefits) And (Entity = 0 And CoCode = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
        
                                                     
                      STRWhere = " WHERE tbbilling.benefitsid = " & Nz(Benefits, 0) & ""
                      
                      
                      
            'filtering by Company Code
            
                
        Case Not IsNull(CoCode) And (Entity = 0 And Benefits = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
        
        
                                                     
                      STRWhere = " WHERE tbbilling.companyID = " & Nz(CoCode, 0) & ""
                        
                                         
            'Filtering by Entity and Benefits
            

        Case Not IsNull(Benefits And Entity) And (CoCode = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
        
                          STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & "" _
                                    & " AND tbbilling.benefitsid = " & Nz(Benefits, 0) & ""
'
'          [B][I] 'Filtering by Entity, Benefits and Company Code[/I][/B]

        Case Not IsNull(Entity And Benefits And CoCode) And (InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
                            
                          
                            

                          STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & "" _
                                    & " AND tbbilling.benefitsid = " & Benefits & "" _
                                    & " AND tbbilling.companyID = " & Nz(CoCode, 0) & ""
                                    
            [B][I]'Filtering by Entity and Company code[/I][/B]
            
        Case Not IsNull(Entity And CoCode) And (Benefits = 0 And InvYR = 0 And InvAmt = 0 And InvNO = 0 And InvMonth = 0)
        

                         STRWhere = " WHERE tbbilling.entityId = " & Nz(Entity, 0) & "" _
                                  & " AND tbbilling.companyID = " & Nz(CoCode, 0) & ""
                                  
                'Filtering by Benefits and Company code
                
                             
        Case Not IsNull(Me.cbobenefitssearch And Me.cbococodesearch)
        

                         STRWhere = " WHERE tbbilling.benefitsid = " & Nz(cbobenefitssearch, 0) & "" _
                                  & " AND tbbilling.companyID = " & Nz(cbococodesearch, 0) & ""
'
'        Case InvYR <> 0
'
'
'            STRWhere = " WHERE Year(tbbilling.Invoicedate) = " & InvYR & ""
'
'        Case InvMonth <> 0
'
'            STRWhere = " WHERE Month(tbbilling.Invoicedate) = " & InvMonth & ""
'
'        Case InvAmt <> 0
'
'            STRWhere = " WHERE tbbilling.Invoiceamount LIKE " & InvAmt & " "
'
'        Case InvNO <> 0
'
'            STRWhere = " WHERE tbbilling.InvoiceNumber LIKE '*" & InvNO & "*' "
'
        
            
        Case Else
         
         FilterData = " SELECT * " _
                & "FROM tbbilling" _
                & " ORDER BY tbbilling.Entityid,  tbbilling.benefitsid DESC, tbbilling.invoicedate "
               
               

            Me.subformbenefitstest.Form.RecordSource = FilterData
            Me.subformbenefitstest.Form.Requery

      
        
        Exit Sub
        
        

End Select


            FilterData = " SELECT * " _
                & "FROM tbbilling" _
               & STRWhere _
               & " ORDER BY tbbilling.Entityid, tbbilling.benefitsid DESC, tbbilling.invoicedate ASC "
               
               

            Me.subformbenefitstest.Form.RecordSource = FilterData
            Me.subformbenefitstest.Form.Requery

 

[June7]

Please post lengthy code between code tags to retain indentation and readability.

Have you step debugged?

 

[VSolano]

Yes I had done that

 

[June7]

And what do you see as content of variables? Why doesn't code do what you expect?

Part of issue is probably using IsNull to test content of variables. Variables explicitly declared cannot be Null. Even Variant type will default to empty string.

 

[VSolano]

When I filtering by Entity and CoCode and Benefits is blank, the code is selecting this block

'Filtering by Entity, Benefits and Company Code

When I am stepping into the code Benefits value is zero

 

[June7]

As I said, those variables cannot be Null, they always have a value even if it is an empty string. An empty string is not same as Null. Variables declared as string will default to empty string and variables set as number will default to 0. They are NEVER Null.