Simple quick question (1 Viewer)

[buratti]

If I have an access query lets say somthing simple like customers which has criteria like Status = 4 (BTW. this would be an Access created query not something created in VBA or other places). Now, I want to base a report on that query. To open the report I have a button on an unrelated form. The code behind that button is:

DoCmd.OpenReport "Card Charge Form", acViewPreview, , "[Status ID]= 2" , acDialog

Will Access Query those already queried records, (with this example showing no records) or will it ignore that first criteria and use the one in the command button?

Essentially I am trying to base a few forms and reports on the same query, but some reports have different criteria than others.

 

[pbaldy]

If the query underlying the report has the criteria of status = 4, opening the report with the status = 2 wherecondition will produce no rows. In other words, the wherecondition is applied to the source of the report. I would use a query with no criteria, and let the wherecondition restrict the records.

 

[buratti]

Thanks for the answer. So for a more real life example, maybe you can solve my problem. I have a query (Charge Summary). I have 1 form and 2 reports based on that one query. Form 1 is just a summary of all records, hence needs no criteria. Report 1 shows records where the status ID equals 4 (which happens to be all records marked "Exported" as the status). The control button that opens report 1 has the criteria in the VBA code behind it. Report 2 shows only 1 record; the record that matches the form the report was opened from. Also, the criteria for that is in the button code that opens that report.

Now... All of that code and reports open correctly. My problem is I also have/need a button that exports records to Excel from that original query with criteria of status equals "ready to process" (or status ID 3)and then updates the status ID of those records from "ready to process" to "Exported" (or status ID 4). The problem is that I cant figure out how to use criteria in an export to function. The way it is now, it will export all the records in the query. i can set the criteria of that original query for just "ready to process", but that will mess up all the other reports and forms based on that query. I can also just make a duplicate copy of the query with the correct criteria, but I would like to keep my database uncluttered with minimal tables, forms, queries, etc.

So, basically, in short, how do I set criteria in an export to function, without changing the source query?

 

[RCheesley]

I think you would need to create a separate query for this purpose - or have the information you wish to use to limit the query being passed by a form, perhaps?