Results 1 to 15 of 15

Select Case not firing when control is null

  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,141

    Select Case not firing when control is null


    I have a simple Select that is examining the content of a bound text box. For some reason I'm not seeing, the case of the control content being Null isn't being recognized. I've run the code in Debug and verified each time that the control value is in fact Null. I tried several expressions, some out of pure desperation. In all cases, execution passed over the intended case of Null.

    The Select head:
    Click image for larger version. 

Name:	000.jpg 
Views:	20 
Size:	17.9 KB 
ID:	36843

    The offending Case:
    Click image for larger version. 

Name:	001.jpg 
Views:	20 
Size:	21.9 KB 
ID:	36844


    One of the code variations trying to get Null recognized that verifies the object is in fact Null:
    Click image for larger version. 

Name:	002.jpg 
Views:	20 
Size:	29.1 KB 
ID:	36845

  2. #2
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,643
    Get the null test out of the Select Case like this:
    Code:
    If len(me.PCCode & vbnullstring) = 0 then
        me.PCCode...
        exit sub
    end if
    Select Case me.PCCode
        case "R" ...

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,566
    Yes, those null values drive me crazy too. I am pretty sure there is a way to do it, but I can never remember.
    One "trick" I will sometimes do is add a space after it (just in the code), so it is not null. So then you just add a blank space at the end of each case, i.e.
    Code:
        Select Case Me.P04 & " "
            Case "R "
                ..."
            Case " "
                ...
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,331
    You may be able handle it with the Else as well.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,141
    Thanks everybody. What made the most sense from a code readability in the context of the functionality was to test and set Me.PCCode to a "C" if the control is Null. Then, the SELECT looks for the CASE of a value of "C" and carries out the required action.

    I.e., If IsNull(Me.PCCode) then Me.PCCode = "C" 'Something is changing, turn on indicator

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,843
    Case Null doesn't work because cannot compare anything to Null. Cannot say: If Textbox = Null Then which is essentially what the Case does.

    http://allenbrowne.com/casu-12.html

    As Paul said, could let Null fall under Case Else

    Do you really want to save dummy data to field?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,141
    I understand and why I orginally coded Case IsNull(Me.PCCode).

    I don't know what you mean:
    Do you really want to save dummy data to field?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,843
    You said you are setting PCCode to the value of "C", and since this is a bound textbox, that value is going into record. If you don't consider that 'dummy' data, then go with.

    Case IsNull(Me.PCCode) = True doesn't work because that returns True or False (-1 or 0), neither of which I assume is a value found in PCCode.

    Consider Paul's suggestion to simply let Null fall under the Case Else structure.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,141
    Yes, I agree with you about using Paul's suggestion.

    There is a set of codes that are set in PCCodes within the app and "C" is one of those codes and needs to be used under certain conditions, most notably when the current code detects that it's currently Null.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,849
    curious to know if you tried Select Case IsNull(PCCode)?
    That should have worked, but probably isn't the test you would want to use in your case because it would mean you're testing only for null or not, which wouldn't allow you to test other values.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,141
    Case IsNull(PCCode) is what I started with and that did not work and is why I tried all sorts of other types of statements. Like "Case Len(Me.PCCode & "") = 0" and a bunch of other "shotgun" approaches. I didn't even consider using "Case Else" as I still had the explicit test for Null on the brain.

    Here's the entire SELECT, which I didn't bother posting earlier:

    Code:
            Select Case Me.PCCode
                Case "R"
                    Me("Chg" & FldName) = True
                    
                    If Me.RptNo > 0 Then
                        Me.PCCode = Me.PCCode & "U"
                    Else
                        Me.PCCode = Me.PCCode & "C"
                    End If
                    
                Case "RU", "RC"
                    Me("Chg" & FldName) = True
                    
                Case "N"
                    If Me.RptNo > 0 Then
                        Me("Chg" & FldName) = True
                        Me.PCCode = Me.PCCode & "U"
                    End If
                    
                Case "NU"
                    Me("Chg" & FldName) = True
                    
                Case Else
                    If IsNull(Me.PCCode) Then
                        Me.PCCode = "C"
                        Me("Chg" & FldName) = True
                    End If
                    
            End Select

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,849
    If you're referring to me regarding
    Case IsNull(PCCode) is what I started with
    that's not what I wrote. You are concentrated on the Case statement, I'm referring to the SELECT statement.

  13. #13
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,141
    I see what you mean. I re-read with a different mindset. And yes, I wouldn't want to test for Null to the exclusion of all the other cases of interest.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,849
    What I found interesting about the exercise is that you can build a SELECT CASE IsNull(something) but you can't make a test (Case) against Null. Would make sense that if you can make the comparison for one, you should be able to do so with the other.

  15. #15
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,141
    Yes, that was more-or-less my thought when I coded the Case when I first encountered the issue.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pl sql case select
    By mrmmickle1 in forum Queries
    Replies: 1
    Last Post: 11-17-2015, 10:14 PM
  2. Select case help
    By killermonkey in forum Programming
    Replies: 7
    Last Post: 10-25-2013, 04:09 PM
  3. VBA for SELECT CASE logic
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 12-04-2012, 08:09 PM
  4. Evaluating Null in a Case Function
    By orcinus in forum Programming
    Replies: 8
    Last Post: 05-11-2012, 09:27 AM
  5. tab control - firing a page on-click event
    By Chuck55 in forum Programming
    Replies: 7
    Last Post: 05-01-2012, 08:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums