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

    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:	21 
Size:	17.9 KB 
ID:	36843

    The offending Case:
    Click image for larger version. 

Name:	001.jpg 
Views:	21 
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:	21 
Size:	29.1 KB 
ID:	36845

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    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,904
    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 " "
                ...

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You may be able handle it with the Else as well.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    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,919
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    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
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    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
    12,737
    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,919
    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
    12,737
    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,919
    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, 11:14 PM
  2. Select case help
    By killermonkey in forum Programming
    Replies: 7
    Last Post: 10-25-2013, 05:09 PM
  3. VBA for SELECT CASE logic
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 12-04-2012, 09:09 PM
  4. Evaluating Null in a Case Function
    By orcinus in forum Programming
    Replies: 8
    Last Post: 05-11-2012, 10:27 AM
  5. tab control - firing a page on-click event
    By Chuck55 in forum Programming
    Replies: 7
    Last Post: 05-01-2012, 09: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
  •  
Other Forums: Microsoft Office Forums