Select Case from RecordSet

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Select Case from RecordSet

  1. #1
    New Member MacroAlan's Avatar
    Join Date
    Aug 2004
    Location
    Missouri, USA
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Select Case from RecordSet

     
    I have all the myriad parameters for some canned queries in a lookup table. In some of the instances, the Branch must be specified and others it is blank. In some instances I am looking whether the Customer exists or not.

    Code:
    Set DB = CurrentDb()
            Set McP = DB.OpenRecordset("tbl_APAP_Param")
                Do While Not McP.EOF
                McP.MoveFirst
                    Select Case McP!Branch
                        Case McP!Branch = Null                    'ERROR here
                            Select Case McP!CustomerList
                                Case False
                                    'Not Customer amd Not Branch Specific
                                    BuildProg "Performing " & McP!Category & " for " & McP!Order_Type
                                    SQLstr = "UPDATE tbl_Sales_Data_APAP LEFT JOIN tbl__Customer_Names ON tbl_Sales_Data_APAP.[Sold to Num] = "
                                    SQLstr = SQLstr & " tbl__Customer_Names.[SoldToCustNbr] SET tbl_Sales_Data_APAP.[Customer]= '" & McP!Category & "'"
                                    SQLstr = SQLstr & " WHERE (((tbl_Sales_Data_APAP.[Order Type])='" & McP!Order_Type & "') AND ((tbl__Customer_Names.SoldToCustNbr) Is Null));"
                                Case True
    What is the correct syntax to check for NULL in Case?

  2. #2
    New Member MacroAlan's Avatar
    Join Date
    Aug 2004
    Location
    Missouri, USA
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select Case from RecordSet

    Eureka! I've got it!

    Found out that I cannot do a Select Case on a NULL. Here is the fix:
    Code:
            Set McP = DB.OpenRecordset("tbl_APAP_Param")
                McP.MoveFirst
                Do While Not McP.EOF
                    If IsNull(McP!Branch) Then                 ' Replacement for Case IsNull
                            Select Case McP!CustomerList
                                Case False                                'Customer is a Yes/No in Lookup table
                                    'Not McN Customer amd Not Branch Specific

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select Case from RecordSet

    Maybe you can. The syntax would be Case Is Not Null (McP!Branch) or something like that, which is what you switched to in you working code. You cannot compare (>, <, =) anything to Null because null is an unknown. I'll have to try it and report back, because now I'm not sure about the suggestion, but I am sure about comparing anything to null.
    Last edited by Micron; Nov 29th, 2017 at 09:07 PM. Reason: clarification
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select Case from RecordSet

      
    Seems you are correct. However, there are ways around it, such as testing for Null and branching accordingly first. You can also wrap the Select Case parameter in the Nz function and substitute an empty string or any suitable value instead. Here's how to use the empty string comparison, assuming the control holds a text value (as in "Other").
    Code:
    Select Case Nz(Forms!form2.SomeField, "")
     Case Is = ""
       MsgBox "Nuttin dere!"
     Case "Other"
      do something for Other
     Case Else
      do whatever
    End Select

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com