Select Case from RecordSet

[MacroAlan]

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.

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?

 

[MacroAlan]

Eureka! I've got it!

Found out that I cannot do a Select Case on a NULL. Here is the fix:

        Set McP = DB.OpenRecordset("tbl_APAP_Param")
            McP.MoveFirst
            Do While Not McP.EOF
                If IsNull(McP!Branch) Then                [B] ' Replacement for Case IsNull[/B]
                        Select Case McP!CustomerList
                            Case False                               [B] 'Customer is a Yes/No in Lookup table[/B]
                                'Not McN Customer amd Not Branch Specific

 

[Micron]

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.

 

[Micron]

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").

Select Case Nz(Forms!form2.SomeField, "")
 Case Is = ""
   MsgBox "Nuttin dere!"
 Case "Other"
  do something for Other
 Case Else
  do whatever
End Select