Combination Curse

[TC]

You code snippet looks wrong to me. induscondition could be (for example):

"[%$##@_Alias].industry = 'bank'"[%$##@_Alias].industry =
'broker[%$##@_Alias].industry = 'leisure'

and that sure won't work! You need some spaces and OR connectors.

However, there are many ways to simplify this. Here is one of the many.

Rename the checkboxes to chk1, chk2, chk3 etc. Put the appropriate search
term (bank, broker) into the Tag property of each checkbox. Then build the
induscondition string, when you need it, like this:

(untested)

dim n as integer
induscondition = ""
for n = 1 to 13 ' or whatever.
with me("chk" & n)
if .value = true then
induscondition = induscondition & _
" OR " & "[%$##@_Alias].industry=""" & .Tag & """"
endif
end with
next

if induscondition <> "" then
induscondition = mid$ (induscondition , 5)
endif

HTH,
TC

I've got 13 boolean variables that either generate a condition for a SQL

query or don't (most will be null-but you never know what a user will want).
When the numbers are smaller I usually use the brute force method as I'm a
VBA novice. Is their a better solution than writing code for the 8000+
combinations. code for reference is below:

Function induscondition() As String

Dim stbank As String
Dim stbroker As String
Dim stins As String
Dim stspecfin As String
Dim stmtg As String
Dim stleisure As String
Dim stretail As String
Dim stmedia As String
Dim sttelecom As String
Dim stindustrials As String
Dim stenergy As String
Dim stute As String
Dim stmisc As String

If Me![chBank] = True Then
stbank = "[%$##@_Alias].industry = 'bank'"
Else
stbank = ""
End If

If Me![Chbroker] = True Then
stbroker = "[%$##@_Alias].industry = 'broker'"
Else
stbroker = ""
End If.......

induscondition = stbank & stbroker & stins & stspecfin & stmtg & stleisure & stretail & _
& stenergy & stute & stmedia & sttelecom &

stindustrials & stmisc

(your code snippet stopped here)

 

[Guest]

I've got 13 boolean variables that either generate a condition for a SQL query or don't (most will be null-but you never know what a user will want). When the numbers are smaller I usually use the brute force method as I'm a VBA novice. Is their a better solution than writing code for the 8000+ combinations. code for reference is below:

Function induscondition() As String

Dim stbank As String
Dim stbroker As String
Dim stins As String
Dim stspecfin As String
Dim stmtg As String
Dim stleisure As String
Dim stretail As String
Dim stmedia As String
Dim sttelecom As String
Dim stindustrials As String
Dim stenergy As String
Dim stute As String
Dim stmisc As String

If Me![chBank] = True Then
stbank = "[%$##@_Alias].industry = 'bank'"
Else
stbank = ""
End If

If Me![Chbroker] = True Then
stbroker = "[%$##@_Alias].industry = 'broker'"
Else
stbroker = ""
End If.......

induscondition = stbank & stbroker & stins & stspecfin & stmtg & stleisure & stretail & _
& stenergy & stute & stmedia & sttelecom & stindustrials & stmisc