Select Case from RecordSet

MacroAlan

New Member
Joined
Aug 30, 2004
Messages
31
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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                [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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top