Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: Using Form Control value to determine query criteria

    I'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.

    I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).

    I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.

    Things I have tried to no avail:
    Putting a Iif statement in the query criteria: gives an error that criteria is too complex
    Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work
    Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could left the query unchanged and pass a criteria in the WhereCondition parameter of the OpenReport method:
    Code:
    If me![Read Only] = True Then
        DoCmd.OpenReport "ReportName",,, "[Ready Pieces] > 0"
    Else
        DoCmd.OpenReport "ReportName"
    End If
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    4
    That's a very easy fix I didn't consider. Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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