VBA Case Statement selecting the wrong block (1 Viewer)

VSolano

Registered User.
Local time
Today, 06:11
Joined
Feb 21, 2017
Messages
85
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


Code:
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
 
Last edited by a moderator:

June7

AWF VIP
Local time
Today, 02:11
Joined
Mar 9, 2014
Messages
5,423
Please post lengthy code between code tags to retain indentation and readability.

Have you step debugged?
 

VSolano

Registered User.
Local time
Today, 06:11
Joined
Feb 21, 2017
Messages
85
Yes I had done that
 

June7

AWF VIP
Local time
Today, 02:11
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 06:11
Joined
Feb 21, 2017
Messages
85
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

AWF VIP
Local time
Today, 02:11
Joined
Mar 9, 2014
Messages
5,423
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.
 

Users who are viewing this thread

Top Bottom