Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-19-2019, 11:18 AM   #1
VSolano
Newly Registered User
 
Join Date: Feb 2017
Posts: 81
Thanks: 7
Thanked 0 Times in 0 Posts
VSolano is on a distinguished road
VBA Case Statement selecting the wrong block

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) & ""
'
'           'Filtering by Entity, Benefits and Company Code

        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) & ""
                                    
            'Filtering by Entity and Company code
            
        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 Uncle Gizmo; 07-19-2019 at 11:34 AM. Reason: Added Code Tags
VSolano is offline   Reply With Quote
Old 07-19-2019, 11:31 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,627
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: VBA Case Statement selecting the wrong block

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

Have you step debugged?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Old 07-19-2019, 11:35 AM   #3
VSolano
Newly Registered User
 
Join Date: Feb 2017
Posts: 81
Thanks: 7
Thanked 0 Times in 0 Posts
VSolano is on a distinguished road
Re: VBA Case Statement selecting the wrong block

Yes I had done that

VSolano is offline   Reply With Quote
Old 07-19-2019, 11:44 AM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,627
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: VBA Case Statement selecting the wrong block

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Old 07-19-2019, 11:50 AM   #5
VSolano
Newly Registered User
 
Join Date: Feb 2017
Posts: 81
Thanks: 7
Thanked 0 Times in 0 Posts
VSolano is on a distinguished road
Re: VBA Case Statement selecting the wrong block

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
VSolano is offline   Reply With Quote
Old 07-19-2019, 12:28 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,627
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: VBA Case Statement selecting the wrong block

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Reply

Tags
select case

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What's wrong with UNION in this case? Unixaix Queries 3 04-11-2019 10:39 AM
LOST! SQL STATEMENT as recordsource to listbox on case statement Shaunk23 Modules & VBA 5 04-11-2012 07:12 AM
Select Case pulling wrong value skwilliams Modules & VBA 6 10-28-2009 07:36 AM
Change Code from IF statement to CASE statement papic1972 Modules & VBA 6 06-16-2008 03:26 PM
Access Block's Selecting a Field breamachine Forms 0 02-14-2007 04:33 AM




All times are GMT -8. The time now is 12:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World