Specify Criteria At Runtime

[EdAROC]

Have an existing report that includes Finished Goods (FG) for Open Orders. Manager requested to be able to run report with either Open Orders FG or ALL FG inventory.

An order's completion status can be C, F, H, O, P, X; C-completed, X-cancelled, the others are various open statuses.

The Open Orders criteria in the query for COMPLETION_STATUS (status of the order) is: <> "C" And <> "X". I realize: In ("F","H","O","P") is equivalent.

Added a frame on the form:
[Include Finished Goods For...]
o Open Orders (Only)
o All Finished Goods

But I don't know how to get from the user's selection (Open or ALL) into the query criteria. Here's coding, when user clicks "Run Report" button, I thought might work (Commented the assignment because of error message:

'Content - All FG or Open Orders FG
If fraContent = 1 Then
'strContent = '"C","F","H","O","P","X"'
Else
'strContent = '"F","H","O","P"'
End If
Me.txtContentHide = strContent

Then in the Criteria of the query would have:
In ([Forms]![Aged FG Open Orders]![txtContentHide])

Nope. That's dead in the water. How close am I?

 

[dhookom]

Don't do anything in you query criteria. Use code to create a where condition in your OpenReport method.

Dim strWhere as String
strWhere = "1=1 "
If Me.fraContent = 1 Then
    strWhere = strWhere & " And COMPLETION_STATUS IN " & _
        "('C','F','H','O','P','X')"
 Else
    strWhere = strWhere & " And COMPLETION_STATUS IN " & _
        "('F','H','O','P')"
End If
DoCmd.OpenReport "rptYourName", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EdAROC]

OK ... Ran into another problem that was messing up the report. (Another linked table, SHIPPING_RELEASES, that needed a phrase for it's Status (Shipped complete or not)). When I got one working, Open or All orders, the other didn't - so I finally revamped the report and summarized the shipping data.

BUT!!! Your coding works great!
And now I understand the criteria argument of the DoCmd.Report.

I replaced the "1=1 " with "". What is the reason you used 1=1 (I would call it a dummy up phrase because it's always true.)

THANKS

 

[dhookom]

I start my where codition with 1=1 so that adding " And ..." doesn't cause an error.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EdAROC]

Ah, yes. Saves having to add code If (len(strWhere) = 0 Then strWhere = {...} Else strWhere = {And ...} for multiple criteria situations. Less code and executes faster, which is more efficient coding. (Yes, I realize that either way takes insignificant time, but, having learned programming in the 70s when it did make a difference I still think of those things).

Thanks again.