Query criteria based on a form field

[pbien]

I am trying to open a report from a form using data from a field on the form as the criteria for the report query. I will try to explain.

I can get the report to open when the form field reads a single criteria like "Cross Country". My problem comes when I try to get the report to open when the field says Varsity Soccer or JV Soccer. If I put *soccer* directly in the query criteria the report opens. If I put *soccer* in the form field and then get the query to read the criteria from the form, the report will not open. I have also tried "Varsity soccer or JV soccer" in the field and it still does not work.

What other wording can I use to get all the Varsity soccer and JV soccer names to open in the report? Or what other approach should I use?

See attached sample database.

Thanks.

Paul

[MarkLiquorman]

To understand what you have to do, you need to look at the SQL for the query behind the report. The WHERE clause is this (I got rid of the extra parentheses to make it easier to read):

WHERE tblStudentInfo.[fall sport]=Forms]![frmPrintSelectedTeamRoster]![CboFallSports]

If "Cross Country" was selected in cboFallSports, Access would evaluate the above line as:

WHERE tblStudentInfo.[fall sport]="Cross Country"

If you were somehow to put "Cross Country or Soccer" in the combo box, Access would treat it as this:

WHERE tblStudentInfo.[fall sport]="Cross Country or Soccer"

But you really need it to read like this:

WHERE tblStudentInfo.[fall sport]="Cross Country" OR tblStudentInfo.[Fall sport]="Soccer"

I would suggest that you don't put the selection criteria in the query; this limits you too much. Instead, build the WHERE clause you want ini code (without the word 'WHERE"), and pass it to the report in the WhereCondition variable of the DoCmd.OpenReport method.

[pbien]

That approach worked.

Thanks.

Paul

[johnhutchison]

A combo box on a form is designed to have just a single value. If you want to be able to select multiple values (sports) you really need to use a multiselect list box instead.

You cannot use mutliselect list boxes as query criteria. Instead you have no choice but to build the Where clause in code.

The code can consist of a series of OR statements, or use the IN syntax.

tblStudentInfo.[fall sport] IN ("Cross Country", "Soccer" )

Post back if you are interested in that approach. The coding is a bit more complicated.