Hoe to Nest Select Case

[Guest]

Currently I am using If..Then..Else statements using 3 variable.
I have 3 variables and would like to use Select Case Statement. VBA help for
select case statement states that Select Case statements can be NESTED.
e.g. var1 = country (ALL, USA, UK, CANADA)
var2 = status (All, Phone, Fax)
var3 = shift (All, Day, Night)

How can I nest select case statements using above variables?
Thanks for help.

 

[John Vinson]

Currently I am using If..Then..Else statements using 3 variable.
I have 3 variables and would like to use Select Case Statement. VBA help for
select case statement states that Select Case statements can be NESTED.
e.g. var1 = country (ALL, USA, UK, CANADA)
var2 = status (All, Phone, Fax)
var3 = shift (All, Day, Night)

How can I nest select case statements using above variables?
Thanks for help.

What's the context, Dave? What are you trying to accomplish? Rather
than stepping through in code, might a Query on a table be better?


John W. Vinson[MVP]

 

[Guest]

I need to pull data from 18 tables. I created an unbound form with combo
boxes to select values. 3 combo boxes are used as criteria that has "ALL" as
one of the values. "ALL" is not in tables. I construct SQL based on the
values from combo boxes and the criteria using If...Then...ElseIf...Else. Is
there a way to use nested Select Case to minimize the code.

Here is the code:

strCompany = Me.cboCompany
strLeadType = Me.cboLead_Type
strStatus = Me.cboStatus
intNum = CInt(Me.txtLimit)
strFormType = "frm" & Me.cboFormType
strCountry = Me.cboCountry 'criteria
strRoom = Me.cboRoom 'criteria
strShift = Me.cboPrintShift 'criteria


If (strCountry = "Not USA" Or strCountry = "Not UK" Or strCountry = "Not
Canada") And strRoom = "ALL" And strPullShift = "ALL" Then
'Country other then USA or UK or Canada, all rooms and both shifts
SQL5 = "SELECT TOP " & intNum & " * FROM [tbl" & strCompany & "]
WHERE Company_Name = '" & strCompany & "' AND Lead_Type =

'" & strLeadType & "' " & _
"AND Status = '" & strStatus & "' AND [Lead_IN_OUT] = 'IN' And
Country <> '" & strCountry & "' " & _
"ORDER BY [" & Me.cboOrderBy & "] " & Me.cboSortBy & ",
[Phone_Number] "

ElseIf (strCountry = "Not USA" Or strCountry = "Not UK" Or strCountry =
"Not Canada") And strPullShift <> "ALL" Then
'Country other than USA or UK or Canada, selected room and shift
SQL5 = "SELECT TOP " & intNum & " * FROM [tbl" & strCompany & "]
WHERE Company_Name = '" & strCompany & "' AND Lead_Type =

'" & strLeadType & "' " & _
"AND Status = '" & strStatus & "' AND [Lead_IN_OUT] = 'IN' And
Country <> '" & strCountry & "' And Room = '" & strRoom & "' And Shift = '"

& strPullShift & "' " & _
"ORDER BY [" & Me.cboOrderBy & "] " & Me.cboSortBy & ",
[Phone_Number] "

ElseIf strCountry = "ALL" And strPullShift = "ALL" Then
'Selected Country, all rooms and both shifts
SQL5 = "SELECT TOP " & intNum & " * FROM [tbl" & strCompany & "]
WHERE Company_Name = '" & strCompany & "' AND Lead_Type =

'" & strLeadType & "' " & _
"AND Status = '" & strStatus & "' AND [Lead_IN_OUT] = 'IN' " & _
"ORDER BY [" & Me.cboOrderBy & "] " & Me.cboSortBy & ",
[Phone_Number] "

ElseIf strCountry = "ALL" And strPullShift <> "ALL" Then
'Selected Country, selected shift and all rooms
SQL5 = "SELECT TOP " & intNum & " * FROM [tbl" & strCompany & "]
WHERE Company_Name = '" & strCompany & "' AND Lead_Type =

'" & strLeadType & "' " & _
"AND Status = '" & strStatus & "' AND [Lead_IN_OUT] = 'IN' And
Shift = '" & strPullShift & "' " & _
"ORDER BY [" & Me.cboOrderBy & "] " & Me.cboSortBy & ",
[Phone_Number] "

Else
'All Countries, rooms and both shifts
SQL5 = "SELECT TOP " & intNum & " * FROM [tbl" & strCompany & "]
WHERE Company_Name = '" & strCompany & "' AND Lead_Type =

'" & strLeadType & "' " & _
"AND Status = '" & strStatus & "' AND [Lead_IN_OUT] = 'IN' " & _
"ORDER BY [" & Me.cboOrderBy & "] " & Me.cboSortBy & ",
[Phone_Number] "

End If

Can you suggest a better way?