Form to dynamically run query with 'OR' clause

[rhewitt]

I have a form that supplies a value to a query that runs a report. In some cases, the user would want to add more than one value to the query. I've added checkboxes that supply an extra combobox for the user to add to the query. In the background the query is formed with an 'OR'.
vs.

The problem is I need to be able to show if there is more than one value displayed on the report. My current solution only shows the second value. Is there a way to show both somehow?

[June7]

For one way to select multiple criteria on one field, review http://allenbrowne.com/ser-50.html

You want each Cooperative to show on separate page of report?

If you need more assistance, fastest and easiest way to resolve would be to provide db so can review code, query, report design.

If you want to provide db, follow instructions at bottom of my post. Identify the objects involved in issue.

[rhewitt]

AtRiskSpeciesFinderSept26.zip

My current plan of attack was just to show the names of the cooperative at the top of the report. I can see how separating them onto their own pages could be useful, but then they could just run the query more than once and get the same result.

[June7]

Do you want to allow only 3 cooperatives to be selected with the 3 comboboxes? A textbox on report could refer to the three comboboxes and concatenate.

=forms!formname!combobox1 & ", " & forms!formname!combobox2 & ", " & forms!formname!combobox3

Yes, user could run query many times to get output for each cooperative but if you could run query and get all printed in one request, isn't that more efficient?

[rhewitt]

Yes you're right it would be more efficient. Does this method allow for conditional statements to test if the boxes are empty?

If cboLCC2 == "" Then
'Don't concatenate

My experience with scripting is mostly limited to Python and I've still a newbie when it comes to Access. Your help has been amazing, thank you.

[June7]

Conditional inclusion of comma would use IIf and get long and complicated. Consider:

a = forms!formname!combobox1
b = forms!formname!combobox2
c = forms!formname!combobox3

= a & IIf(IsNull(b) And IsNull(c), "", IIf(Not IsNull(a), ", ", "")) & b & IIf(IsNull(c), "", IIf(Not IsNull(b), ", ", "")) & c

Put the combobox name references in place of the respective letter variables.

[rhewitt]

Thanks, WOW, that's quite an expression.

Seems as if with the substitution of combobox references for letter variables I've gone over the length limit for the expression. I'll have to think of a work around -- I'm sure the IIf IsNull will certainly come in handy.

[rhewitt]

Ok here's my hacked solution...

I've concatenated the combobox references with carriage returns, and set the text box property 'Can Grow' to true. Now if more than one box is checked, the cooperatives are listed vertically (not ideal, but close enough).

=[Forms]![FormName]![cbo] & Chr(13) & Chr(10) & [Forms]![FormName]![cbo2] & Chr(13) & Chr(10) & [Forms]![FormName]![cbo3]

The problem I had here was that the Page Header wouldn't grow with the growing textbox. For reasons I may never understand, the Page Header has no Property "Can Grow." The solution is to create a new arbitrary Group Header, which does support the Can Grow Property.

New problem! If a user checks queries based on 2 or more cooperatives, then reverts back to one -- the names of the 2nd or 3rd cooperatives are still shown in the report. I need to to do a conditional on the checkboxes. I keep getting back an error that I have the wrong number of arguments.

=[Forms]![SpeciesByLCC]![cboLCC] & IIf([Forms]![SpeciesByLCC]![check1]) Then Chr(13) + Chr(10) & [Forms]![SpeciesByLCC]![cboLCC2]

Thanks!
Roy

[June7]

There is no 'Then' in an IIf function, just commas. Use ampersand for all concatenation operations. An IIf has 3 arguments:

IIf(condition, result if true, result if false)

Try:

=[Forms]![SpeciesByLCC]![cboLCC] & IIf([Forms]![SpeciesByLCC]![check1], Chr(13) & Chr(10) & [Forms]![SpeciesByLCC]![cboLCC2], "")

[rhewitt]

Works beautifully!