Multiple IIf

  • Thread starter Thread starter .Len B
  • Start date Start date
L

.Len B

I have a report whose filter is being built by a form with 2
txt boxes for a date range and 8 chk boxes to specify which
Roles to include in the data.

Here is what I have so far.

="(ContactDate >= #" & [txtOpenDate] & "#) AND (" &
"ContactDate <= #" & [txtCloseDate] & "#) AND (" &
IIf([chkRole2],"(txtRoleID = 2) OR "," ") &
IIf([chkRole3],"(txtRoleID = 3) OR "," ") &
IIf([chkRole4],"(txtRoleID = 4) OR "," ") &
IIf([chkRole5],"(txtRoleID = 5) OR "," ") &
IIf([chkRole6],"(txtRoleID = 6) OR "," ") &
IIf([chkRole7],"(txtRoleID = 7) OR "," ") &
IIf([chkRole8],"(txtRoleID = 8)"," ") & ")"

chkRole1 has label 'ALL' and checks or unchecks 2 thru 8 from where
the user can subtract or add more Roles. I don't see it being
useful in the logic.

I am having trouble with the 'OR' portions. As it stands, unless
Role 8 is checked there is a trailing OR. If I move the ORs to the
beginning I have a leading OR unless Role 2 is Checked.

Is there some other logic I can use to generate the desired string?
 
Do the code in VBA:

Just always add the " OR " and strip off 4 characters at the end
strText = Left(strText,Len(StrText) - 4)

Also if it's possible to not have any checkboxes checked you need logic for
that as well.

You could probably use a loop to loop through the controls that have similar
names (if looking for another alternative to how to write the logic).
I would just use regular if statements and add to string variable.

HTH,
Mark
 
Thanks for the suggestions Mark. The code I'm using is in fact the
control source of a text box. It is built as data is entered in the
form. Then the report filter property is set to that string when the
report becomes visible. I guess I could use the form's LostFocus
event to tweak the string.

It is possible but illogical to have zero boxes checked and I haven't
catered specifically for it. Happily, it defaults to the same as having
all checked.

What I've done so far now is to always add the OR and then append an
always-false term (RoleID=9). It works but I'll think about the vba
possibilities.
Thanks
--
Len
______________________________________________________
remove nothing for valid email address.
| Do the code in VBA:
|
| Just always add the " OR " and strip off 4 characters at the end
| strText = Left(strText,Len(StrText) - 4)
|
| Also if it's possible to not have any checkboxes checked you need logic
for
| that as well.
|
| You could probably use a loop to loop through the controls that have
similar
| names (if looking for another alternative to how to write the logic).
| I would just use regular if statements and add to string variable.
|
| HTH,
| Mark
| | >I have a report whose filter is being built by a form with 2
| > txt boxes for a date range and 8 chk boxes to specify which
| > Roles to include in the data.
| >
| > Here is what I have so far.
| >
| > ="(ContactDate >= #" & [txtOpenDate] & "#) AND (" &
| > "ContactDate <= #" & [txtCloseDate] & "#) AND (" &
| > IIf([chkRole2],"(txtRoleID = 2) OR "," ") &
| > IIf([chkRole3],"(txtRoleID = 3) OR "," ") &
| > IIf([chkRole4],"(txtRoleID = 4) OR "," ") &
| > IIf([chkRole5],"(txtRoleID = 5) OR "," ") &
| > IIf([chkRole6],"(txtRoleID = 6) OR "," ") &
| > IIf([chkRole7],"(txtRoleID = 7) OR "," ") &
| > IIf([chkRole8],"(txtRoleID = 8)"," ") & ")"
| >
| > chkRole1 has label 'ALL' and checks or unchecks 2 thru 8 from where
| > the user can subtract or add more Roles. I don't see it being
| > useful in the logic.
| >
| > I am having trouble with the 'OR' portions. As it stands, unless
| > Role 8 is checked there is a trailing OR. If I move the ORs to the
| > beginning I have a leading OR unless Role 2 is Checked.
| >
| > Is there some other logic I can use to generate the desired string?
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
|
|
 
If you must keep the logic in the control source, then add an extra (1 = 2)
condition to go after the last OR.

VBA is the better way to go. Just build the filter string and then use it
when you call the report.

Mark
.Len B said:
Thanks for the suggestions Mark. The code I'm using is in fact the
control source of a text box. It is built as data is entered in the
form. Then the report filter property is set to that string when the
report becomes visible. I guess I could use the form's LostFocus
event to tweak the string.

It is possible but illogical to have zero boxes checked and I haven't
catered specifically for it. Happily, it defaults to the same as having
all checked.

What I've done so far now is to always add the OR and then append an
always-false term (RoleID=9). It works but I'll think about the vba
possibilities.
Thanks
--
Len
______________________________________________________
remove nothing for valid email address.
| Do the code in VBA:
|
| Just always add the " OR " and strip off 4 characters at the end
| strText = Left(strText,Len(StrText) - 4)
|
| Also if it's possible to not have any checkboxes checked you need logic
for
| that as well.
|
| You could probably use a loop to loop through the controls that have
similar
| names (if looking for another alternative to how to write the logic).
| I would just use regular if statements and add to string variable.
|
| HTH,
| Mark
| | >I have a report whose filter is being built by a form with 2
| > txt boxes for a date range and 8 chk boxes to specify which
| > Roles to include in the data.
| >
| > Here is what I have so far.
| >
| > ="(ContactDate >= #" & [txtOpenDate] & "#) AND (" &
| > "ContactDate <= #" & [txtCloseDate] & "#) AND (" &
| > IIf([chkRole2],"(txtRoleID = 2) OR "," ") &
| > IIf([chkRole3],"(txtRoleID = 3) OR "," ") &
| > IIf([chkRole4],"(txtRoleID = 4) OR "," ") &
| > IIf([chkRole5],"(txtRoleID = 5) OR "," ") &
| > IIf([chkRole6],"(txtRoleID = 6) OR "," ") &
| > IIf([chkRole7],"(txtRoleID = 7) OR "," ") &
| > IIf([chkRole8],"(txtRoleID = 8)"," ") & ")"
| >
| > chkRole1 has label 'ALL' and checks or unchecks 2 thru 8 from where
| > the user can subtract or add more Roles. I don't see it being
| > useful in the logic.
| >
| > I am having trouble with the 'OR' portions. As it stands, unless
| > Role 8 is checked there is a trailing OR. If I move the ORs to the
| > beginning I have a leading OR unless Role 2 is Checked.
| >
| > Is there some other logic I can use to generate the desired string?
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
|
|
Click to expand...
 
Back
Top