Criteria....Sometimes YES, sometimes NO

[bcarter17]

Hello,
On some of our reports, we query a particular customer and in some instances, we need to request the report show a particular PO number. At other times, we need the report to print all PO numbers. Also, some customers don't use PO numbers so that field is blank. I tried to enter criteria request like [Enter PO Number], but if I leave it blank as I expected it returns nothing.



Example - Customer Smith has multiple PO's 001, 002, 003, 004 and 005. I might need to just print 002, or at times I need to print all 5. Then, sometimes Smith will not use a PO number and the field is blank. So I would need to remove the criteria for the user and pull all 5 PO's and the orders with no PO number into one report.

Any workarounds on this, I used to have this issue with Access 2003 and never found a good fix. I now am using 2007.

[ranman256]

I have a tPicked list, user adds items to it by dbl-clicking (like POs) runs append qry.
you then join tPicked to the main data table and only those item will pull.

[bcarter17]

That's a great workaround. So how would this work if some of the selections I needed, the field was blank (for example in your application, US Virgin Islands)?

Also, if the report needed to pull "ALL", then how do you set it up to not use the tPickedList? VBA code prior to execution to make a decision on using one method or another?

Can you show me the setup and code for your tPickedList form?

[orange]

Can you post a copy of your database (zip format)?

[bcarter17]

Here you go!

[ssanfu]

On some of our reports, we query a particular customer and in some instances, we need to request the report show a particular PO number. At other times, we need the report to print all PO numbers. Also, some customers don't use PO numbers so that field is blank.

How to get to the report(s)? What report names?

[NoellaG]

Have you already tried something as

"*" & NZ([Fieldname) & "*"

with the like operator?

[Cottonshirt]

you want a report that runs from a parameter, AND you want a blank parameter to mean ALL records.


this is the example I use in a query, I guess it should work for a report. pay attention to the WHERE statement:

SELECT tbl_spool_file.supplier_in, tbl_spool_file.date_in, tbl_spool_file.po_number, tbl_spool_file.line_po, tbl_spool_file.part_number
FROM tbl_spool_file
WHERE ((([enter a po number]) Is Null)) OR (((tbl_spool_file.po_number)=[enter a po number]) AND (([enter a po number]) Is Not Null))
ORDER BY tbl_spool_file.po_number, tbl_spool_file.line_po;

good luck with your project



Cottonshirt