Better way to write this VBA

H

Heather

Is there a better way to choose the variables that I want?
Here's what I have so far and I need to expand it to 6
different choices rather than 3
'MemberInfo data for the GroupID
If ClassVar1 = "" And ClassVar2 = "" And ClassVar3 = "" _
And PlanVar1 = "" And PlanVar2 = "" And PlanVar3 = "" _
And BusVar1 = "" And BusVar2 = "" And BusVar3 = "" _
Then

strSQL = "Select SaCd, count(MemId)" & _
"From MemberInfoFacets " & _
"Where GroupID IN ('" & GroupVar1 & "','" &
GroupVar2 & "', '" & GroupVar3 & "') " & _
" AND MemDate = " & INDV & _
" GROUP BY SaCd"

'MemberInfo data for the GroupID and ClassID
ElseIf PlanVar1 = "" And PlanVar2 = "" And PlanVar3 = "" _
And BusVar1 = "" And BusVar2 = "" And BusVar3 = "" _
Then


strSQL = "Select SaCd, count(MemId)" & _
"From MemberInfoFacets " & _
"Where GroupID IN ('" & GroupVar1 & "','" &
GroupVar2 & "', '" & GroupVar3 & "') " & _
" And ClassID IN ('" & ClassVar1 & "','" &
ClassVar2 & "', '" & ClassVar3 & "') " & _
" AND MemDate = " & INDV & _
" GROUP BY SaCd"

'MemberInfo data for the GroupID and BusCat
ElseIf ClassVar1 = "" And ClassVar2 = "" And ClassVar3
= "" _
And PlanVar1 = "" And PlanVar2 = "" And PlanVar3 = "" _
Then

strSQL = "Select SaCd, count(MemId)" & _
"From MemberInfoFacets " & _
"Where GroupID IN ('" & GroupVar1 & "','" &
GroupVar2 & "', '" & GroupVar3 & "') " & _
" And BusCat IN ('" & BusVar1 & "','" &
BusVar2 & "', '" & BusVar3 & "') " & _
" AND MemDate = " & INDV & _
" GROUP BY SaCd"
'MemberInfo data for the GroupID and ClassID and PlanID
Else

strSQL = "Select SaCd, count(MemId)" & _
"From MemberInfoFacets " & _
"Where GroupID IN ('" & GroupVar1 & "','" &
GroupVar2 & "', '" & GroupVar3 & "') " & _
" And ClassID IN ('" & ClassVar1 & "','" &
ClassVar2 & "', '" & ClassVar3 & "') " & _
" And PlanID IN ('" & PlanVar1 & "','" &
PlanVar2 & "', '" & PlanVar3 & "') " & _
" AND MemDate = " & INDV & _
" GROUP BY SaCd"

End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help please assigning column and rows 0
lookup 1
Query not updateable in Access 2000 1
Query not updateable in Access 2000 1
Combox box value used in Query 1
Building WHERE string; Passing Combo Box Value to a Query 1
Help on "Overflow error" due to my poor Memory Var handling or other unknown 5
a better way to query? 4

Top