Results 1 to 5 of 5
  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 fails to trap a null value?

    Select examines for Null as first Case, so how did the code advance to point of failure? Error is "Invalid use of null".



    Click image for larger version. 

Name:	000.jpg 
Views:	13 
Size:	106.4 KB 
ID:	46480

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Okay, Select Case doesn't work like that.

    You pass Null to the Select Case and the first condition it tries to match is an expression that calculates to either True or False. Cannot compare Null to anything because Null has nothing to compare.

    If you pass a date such as 10/24/2021 to the Select, it won't match the InStr() >0 expression either because it also evaluates to True or False.

    So EVERYTHING ends up in Else.

    Use If Then ElseIf Else.
    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.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    WaitDate is variant input to a public function embedded in a query. I don't necessarily know if the table field is null or zero-length. I'm expecting the "IsNull(WaitDate)" to be either True or False. Are you telling me I can't test that way?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No, you can't. Imagine the test is
    Code:
    Select Case colour
     Case "red"
       do something
     Case "blue"
      do this
     Case "green"
      do that
     Case Else
      do nothing
    End Select
    Any colour value will evaluate as that is the test. You cannot substitute other "things" like logical decisions, function tests, mismatched data types, etc. for what you're test is about (the Case statements).

    You can test for null if that is the test. It might make more sense if I reverse the situation and say if you're testing for Null, why would you specify a Case for "red".
    Perhaps make the test

    Select Case Nz(WaitDate, 0)
    Then the value evaluated by the Case statements will either be 0 or a valid date. With valid dates, you can do your tests and/or if 0 (not null) do whatever
    Last edited by Micron; 10-24-2021 at 09:13 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I see your point. Funny I've never had a "Select Mix" like this in the past. I'll recode appropriately.

    Bill

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

Similar Threads

  1. Select Case without case
    By DMT Dave in forum Access
    Replies: 4
    Last Post: 11-13-2019, 08:21 PM
  2. Select Case not firing when control is null
    By GraeagleBill in forum Programming
    Replies: 14
    Last Post: 01-10-2019, 01:04 AM
  3. email program fails with invlaide use of null
    By Lou_Reed in forum Access
    Replies: 40
    Last Post: 06-12-2017, 11:30 AM
  4. Pl sql case select
    By mrmmickle1 in forum Queries
    Replies: 1
    Last Post: 11-17-2015, 11:14 PM
  5. Evaluating Null in a Case Function
    By orcinus in forum Programming
    Replies: 8
    Last Post: 05-11-2012, 10:27 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