If-Then Loop... Simplified?

[thenman]

I need a simplified way of writing and IF THEN loop

I have 10 checkboxes that are search criteria for my database. So 100 different possible combinations of search criteria. For each checkbox that is true, it will paste a piece of a SQL statement based on user chosen range, that is sent to Forms!Mailer.RecordSource after opening the window. (the variable chkDATE and DATERANGE are examples of this). The variables attached to each check box are also missing, but I assume you don't need them to get what I am trying to accomplish.

I hope that isn't too vauge. I just finished writing a long ass post about what I was doing, including my code sample and this website made me log in again, which deleted everything I had written.

Let me know if you need more information. Here is a piece of sample code.... Most of the SQL stuff isn't written yet, I wanted to tackle the IF the problem first. The statement has to start with WHEN (strSQL) then each variable, separated by AND (strSQL2).

Private Sub Command1_Click()
Dim Value1 As String
Dim value2 As String
 
Value1 = Text4 'the starting value of the date range from a date picker
value2 = Text6 'the ending value of the date range from a date picker
 
strSQL = " Where "
strSql2 = " And "
 
If chkDate = True Then
DateRange = "(([Table 2].[Contacted Date]) Between #" & Value1 & "# And #" & value2 & "#)"
Else
chkDate = ""
End If
 
'clears the string
SqlSTRING = ""
 
If chkDate = True And ChkGood = True And ChkOk = True And ChkBad = True And ChkContacted = True Then
SqlSTRING = strSQL & DateRange & strSql2 & CGood & strSql2 & CBad & strSql2 & COk & strSql2 & CContacted
 
'1 false
ElseIf chkDate = True And ChkGood = True And ChkOk = True And ChkBad = True And ChkContacted = False Then
SqlSTRING = strSQL & DateRange & strSql2 & CGood & strSql2 & COk & strSql2 & CBad
 
........Lots of if then statements in between.......
 
 
'all false
ElseIf chkDate = False And ChkGood = False And ChkOk = False And ChkBad = False And ChkContacted = False Then
SqlSTRING = ""
End If
 
 
DoCmd.OpenForm "mailer"
Forms!Mailer.RecordSource = "SELECT Boise.OWNERNM, Boise.OWNRST, Boise.OWNERADR, [Table 2].Contacted, [Table 2].[Contacted Date], Boise.OWNERNM1, Boise.OWNERCTY, Boise.OWNERZIP, Boise.ADDRESS, Boise.CITY, Boise.STATE, Boise.ZIPCODE FROM Boise INNER JOIN [Table 2] ON Boise.RECNO=[Table 2].RECNO" & SqlSTRING & ";"
DoCmd.Close acForm, "criteria", acSaveNo
Form_Mailer.SetFocus
End Sub

 

[Tombra]

I don't know if I undestand you precisely but maybe sth like that

MySqlString=""
SqlWhere="Where "
SqlAnd=" And "

If checkbox1=true then
MySqlString = SQLWhere & "Condition1"
end if

If checkbox2=true then
if MySqlString="" then
MySqlString = SQLWhere
else
MySqlString = MySqlString & SQLAnd
end if
mysqlString = MySqlString & "Condition2"
end if

etc....

 

[Galaxiom]

One simplifcation is to drop the "= true" from the If statements.
If statements refering to the state of a checkbox being true can simply be expressed as:
If checkboxname Then

Normally you would build up the statement based on each individual independent checkbox. Each checkbox adds another piece to the end of the where clause (as Tombra explains above).

Another way is to record all the control names and their associated sql phrases in a table. Then use a For Each Control loop to return the pharases for assembly in the VBA.

One variant of this is to record the sql phrase in the tag property of the control rather than in a table. It makes the vba very tidy but is a bit clumsier to edit the sql phrases than keeping them in a table.

However I gather you may have some complex interaction between the checkbox selections giving you greif. Hence the alarming idea of a hundred individually crafted permutations.

If so then closely reconsider the structure of your database. Perhaps there is a better table structure that leads to simpler queries. The structure is often the problem when queries become unmanageable.

Otherwise (a longshot), could you somehow express this logic in tables? Then apply the sql from a table of phrases with the appropriate one selected by the querying this logic.

Hope this gives you some ideas to work with.

 

[thenman]

I didn't know you could drop the true from a statement, that helps a bit. Thanks.

Even if I drop the true though, what you're suggesting is basically what I am doing now: For each true statement, add a piece of the SQL statement. The problem is, that there are 100 possible combinations of that SQL statement, and to cover each possibility I need to write 100 IF-THEN statements.

Ex. For 3 check boxes True (of 10) I would need to write 30 if then statements, here are 8 based just on checks 1 & 2 true, with a 3rd check that changes:

if check1 and check2 and [B]check3[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition 2 & sqlAnd & [B]Condition 3[/B]
 
else if check1 and check2 and [B]check4[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition 2 & sqlAnd & [B]Condition 4[/B]
 
else if check1 and check2 and [B]check5[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition 2 & sqlAnd & [B]Condition 5[/B]
 
else if check1 and check2 and [B]check6[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition 2 & sqlAnd & [B]Condition6[/B]
 
else if check1 and check2 and [B]check7[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition2 & sqlAnd & [B]Condition7[/B] 
 
else if check1 and check2 and [B]check8[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition2 & sqlAnd & [B]Condition8[/B]
 
else if check1 and check2 and [B]check9[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition2 & sqlAnd & [B]Condition9[/B]
 
else if check1 and check2 and [B]check10[/B] then
sqlStatement = SqlWhere & condition1 & sqlAnd & Condition2 & sqlAnd & [B]Condition10[/B]
 
ETC...

What I am looking for really is something like this, a function that will simplified the 120+ lines of code down to something that is 30 lines:

For each CHECKBOX = True then
sqlStatement = "Where" & Each statement that is true, separated by And;
where
checkbox1 = "this statement"
checkbox2 = "this other statement"
checkbox3 = "etc"
end where
Else All-Checkboxes = False Then
sqlStatement = ""
End For each

Maybe someone can come up with something like that. I have the idea of how it should work in my head, I just don't know how to translate that idea into code.

 

[thenman]

Galaxiom, could you expand on your IF THEN statement? A simple example would help me wrap my head around what you mean.


Thanks for all of the help so far everyone. I appreciate the effort that everyone puts into this forum.

 

[Atomic Shrimp]

There are 10 checkboxes, each corresponding to a condition component.

Why do you need to deal with all of the combinations - just build your where statement based on the individual checkboxes, so something like (aircode):

if check1 then
strcondition = [condition1]
end if

if check2 then
if strcondition = "" then
strcondition = [condition2]
else
strcondition = strcondition & " and " & [condition2]
end if
end if

if check3 then
if strcondition = "" then
strcondition = [condition3]
else
strcondition = strcondition & " and " & [condition3]
end if
end if

etc

 

[thenman]

One of the problems right now with my database is that the information I get is from a website that predetermines how the tables are built. I pay for a service that provides names and information for me, and the format of their table is what dictates the format of my tables. Eventually I could write a form that imports these tables into my access file and sorts to the related field and tables, but I need to get these things fuctioning first and continue my mailings.

I will keep that in mind though.

 

[thenman]

atomic shrimp,

What you just wrote is bascially what I am looking for, but how do I introduce WHERE into the first statement?

maybe at the end of building the strcondition,

if strcondition = "" then
strcondition = ""
else
strcondition = "Where " & strcondition

?? something like that ??

How do you express not empty for a condition? It is something I have been having a problem with.

if strconditon = not null then
strcondition = "Where " & strcondition
else
strcondition = ""
end if


Thanks, we are getting closer to what I am thinking of

 

[Atomic Shrimp]

I usually ignore the 'where' until the end - indeed, I don't worry about the rest of the SQL expression until I've built the conditional part.

So you start with an empty string and add the conditions one by one as appropriate - after the first one*, you need to check if the string you're building is already non-empty at each stage, and add an 'and' first - then when you're completely done building the conditional expression, embed it into your query.


*In fact, it becomes easier just to build all the bits in the same way and not worry that an 'and' will never be required at the start.
So in pseudocode:

StrCond = ""

If [some condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some appropriate part expression]"

If [some other condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some other appropriate part expression]"

If [some blue condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some appropriate blue part expression]"

If [some vanilla condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some appropriate vanilla part expression]"

... etc ...

strSQL = "Select * from [yourtablename] "
strSQL = strSQL & " Where " & strCond
strSQL = strSQL & ";"

 

[thenman]

I think I got it. This seems like it should work, I will go through and write the code and let you know how it goes. Thanks a lot for the help gents, it is much appreciated, and hats of to atomic shrimp for the answer, well done.

They should do a "Karma" system on this board. If you answer my question, I can give you a good karma point for helping, or a negative karma point if you are a dick or whatever.

 

[thenman]

One thing that I can see already that has already caused me problems is if there is an AND statement without something following it...

So what I am thinking is this:

If Checkbox1 = true then

if sqlStatement = "" then
sqlStatement = Condition1

else if sqlstatement <> "" then
sqlStatement = sqlStatement & " And " & condition1

end if
end if

Basically the same as what you were suggesting, except this should keep the " AND " part of the statement between other statements.

 

[Galaxiom]

If Checkbox1 = true then

if sqlStatement = "" then
sqlStatement = Condition1

else if sqlstatement <> "" then
sqlStatement = sqlStatement & " And " & condition1

end if
end if

But this won't work for adding other phrases.

One way to do it is include "AND " on the end of every phrase. This means the first one never has an AND before it but the last one has a redundant "AND " at the end. This is easily removed after the statement is assembled.

If Right(sqlStatement,4) = "AND " Then: sqlStatement = Left(sqlStatement, Len(sqlStatement)-4)

 

[Atomic Shrimp]

I think the pseudocode I posted in #9 will handle 'and' conjunctions where and if appropriate - because the middle line in (as highlighted below) in each block is an inline (standalone) If-Then:

If [some condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some appropriate part expression]"
end if

So if the strCond is empty, just the expression (green line) gets added - if it's not empty (i.e. it already contains something from previous blocks of code), the middle line adds the 'and' - and the last (green) line still adds the expression.

I did miss out the End Ifs from each block of code - so it should actually look like this:

StrCond = ""

If [some condition] then
	if strCond <> "" then strCond = strCond & " and "
	strCond = strCond & "[some appropriate part expression]"
End If

If [some other condition] then
	if strCond <> "" then strCond = strCond & " and "
	strCond = strCond & "[some other appropriate part expression]"
End If


If [some blue condition] then
	if strCond <> "" then strCond = strCond & " and "
	strCond = strCond & "[some appropriate blue part expression]"
End If


If [some vanilla condition] then
	if strCond <> "" then strCond = strCond & " and "
	strCond = strCond & "[some appropriate vanilla part expression]"
End If

 

[ChrisO]

You should be able to start the SQL with: -

StrCond = "Where 1 = 1"

and then AND in the other conditions.

But I’d like to see the actual conditions being used.

Regards,
Chris.

 

[Atomic Shrimp]

I usually ignore the 'where' until the end - indeed, I don't worry about the rest of the SQL expression until I've built the conditional part.

So you start with an empty string and add the conditions one by one as appropriate - after the first one*, you need to check if the string you're building is already non-empty at each stage, and add an 'and' first - then when you're completely done building the conditional expression, embed it into your query.


*In fact, it becomes easier just to build all the bits in the same way and not worry that an 'and' will never be required at the start.
So in pseudocode:

StrCond = ""

If [some condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some appropriate part expression]"

If [some other condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some other appropriate part expression]"

If [some blue condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some appropriate blue part expression]"

If [some vanilla condition] then
if strCond <> "" then strCond = strCond & " and "
strCond = strCond & "[some appropriate vanilla part expression]"

... etc ...

strSQL = "Select * from [yourtablename] "
strSQL = strSQL & " Where " & strCond
strSQL = strSQL & ";"

Modified end bit in light of ChrisO's last post:

strSQL = "Select * from [yourtablename] "
if strcond <>"" then strSQL = strSQL & " Where " & strCond
strSQL = strSQL & ";"

The 'Where 1=1' thing is an interesting solution, but I'm wary of it, for a couple of reasons:

• It's the sort of thing an inexperienced programmer inheriting your code might edit out on the grounds that it appears redundant.
• It resembles an SQL injection attack - so whilst it's perfectly fine to do it when you're connecting to your own database, the technique might trip up if used in applications with different database back ends.

 

[ChrisO]

Perhaps so but I’d like to see the actual conditions being used.

I really think that the question so far is poorly defined and was hoping to get at least three conditions that actually work.

In post #4 there is an implied ‘first True out’ structure meaning when we hit a True we don’t continue.
This seems to also mean that check3 has a higher priority than check10.
Also, check1 and check2 are reprocessed in each ElseIf; but why?
If either check1 IOR check2 is False then none of the If/ElseIf’s will be True.
Hence, the test for check1 and check2 can be moved so that test encloses all 8 other tests.
This then leaves only the test for check3 through check10 to be done.
But we should be able to test for check3 through check10 in a loop using 3 through 10 as a subscript to ‘check’.
If we find a True in that loop we can exit the loop to enforce the implied priority.

Now if all the possible states of check1 and check2 are important then we would end up with six such loops assuming that one or the other or both is/are unbound or set to triple state.
That number of six loops can be reduced to four loops by assuming Null to be False if that is appropriate under the circumstances.

Then the question might be asked; is there an implied priority in the four possible states in check1 and check2?
If so then we should be able to do that in a Select Case True structure.

But, to me, it depends on the as yet unstated requirements.

More information required please.

Regards,
Chris.