Please don't shout!

[baggyboy26]

I'm completely self taught ( I can hear you groan) and I'm sure there must be an easier way to do this. When I first started my project the simple Select Case function did just the job. But then as my users demanded more and more options my select case became nested and grew very quickly!!! Basically i have 10 combo boxes that are used to filter a form. Here's a snippet of the code. I know it is ugly, sorry.

Dim WhichData As String
Select Case FSMTrue
Case Is = ""
Select Case GenderTrue
Case Is = ""
Select Case Class
Case Is = "All"
Select Case Year
Case Is = "All"
WhichData = "WHERE (((ass" & [CalendarYear] & ".Year)=""Year 1"" Or (ass" & [CalendarYear] & ".Year)=""Year 2"" Or (ass" & [CalendarYear] & ".Year)=""Year 3"" Or (ass" & [CalendarYear] & ".Year)=""Year 4"" Or (ass" & [CalendarYear] & ".Year)=""Year 5"" Or (ass" & [CalendarYear] & ".Year)=""Year 6""))"
Case Else
WhichData = "WHERE (((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """))"
End Select
Case Else
Select Case Year
Case Is = "All"
WhichData = "WHERE (((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """))"
Case Else
WhichData = "WHERE (((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """)) AND (((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """))"
End Select
End Select
Case Else
Select Case Class
Case Is = "All"
Select Case Year
Case Is = "All"
WhichData = "WHERE (((ass" & [CalendarYear] & ".Year)=""Year 1"" Or (ass" & [CalendarYear] & ".Year)=""Year 2"" Or (ass" & [CalendarYear] & ".Year)=""Year 3"" Or (ass" & [CalendarYear] & ".Year)=""Year 4"" Or (ass" & [CalendarYear] & ".Year)=""Year 5"" Or (ass" & [CalendarYear] & ".Year)=""Year 6"") AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
Case Else
WhichData = "WHERE (((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """) AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
End Select
Case Else
Select Case Year
Case Is = "All"
WhichData = "WHERE (((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """) AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
Case Else
WhichData = "WHERE (((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """) AND ((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """) AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
End Select
End Select
End Select
Case Else
Select Case GenderTrue
Case Is = ""
Select Case Class
Case Is = "All"
Select Case Year
Case Is = "All"
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Year)=""Year 1"" Or (ass" & [CalendarYear] & ".Year)=""Year 2"" Or (ass" & [CalendarYear] & ".Year)=""Year 3"" Or (ass" & [CalendarYear] & ".Year)=""Year 4"" Or (ass" & [CalendarYear] & ".Year)=""Year 5"" Or (ass" & [CalendarYear] & ".Year)=""Year 6""))"
Case Else
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """))"
End Select
Case Else
Select Case Year
Case Is = "All"
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """))"
Case Else
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """) AND ((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """))"
End Select
End Select
Case Else
Select Case Class
Case Is = "All"
Select Case Year
Case Is = "All"
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Year)=""Year 1"" Or (ass" & [CalendarYear] & ".Year)=""Year 2"" Or (ass" & [CalendarYear] & ".Year)=""Year 3"" Or (ass" & [CalendarYear] & ".Year)=""Year 4"" Or (ass" & [CalendarYear] & ".Year)=""Year 5"" Or (ass" & [CalendarYear] & ".Year)=""Year 6"") AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
Case Else
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """) AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
End Select
Case Else
Select Case Year
Case Is = "All"
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """) AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
Case Else
WhichData = "WHERE (((dataPupils.[Eligible for free meals])=""" & Me.FSMTrue & """) AND ((ass" & [CalendarYear] & ".Reg)=""" & Me.Class & """) AND ((ass" & [CalendarYear] & ".Year)=""" & Me.Year & """) AND ((dataPupils.Gender)=""" & Me.GenderTrue & """))"
End Select
End Select
End Select

End Select

I then use WhichData in my SQL statement and requery the form.

Thanks for guidance in advance.

Sometimes the answer stares you right in the face!!!

 

[dhookom]

I'm not sure of all you logic in the code but if "All" is selected in a combo box, why even add anything to a where clause? Can't you just ignore any criteria against a field?

Also, it looks like your table structure might be un-normalized. Field names shouldn't contain data values like years.

You have also used reserved words as field names "Class", "Year". Check Allen Browne's reserved words list

It might help if you:
[li]renamed your combo boxes to "cbo...." so your code would be easier to read[/li]
[li]place "Me." in front of you controls name like "Me.cboClass" so your code would be easier to read[/li]
[li]Provided your combo box names and which fields you wanted to filter from the combo boxes[/li]

Duane
Hook'D on Access
MS Access MVP