Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That approach worked.

    Thanks.

    Paul

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •