How Do I ... Criteria "A" and ("B" or "C") in Grid

[EdAROC]

Creating a report to print labels (to go on manilla folders) for "active" vendors. Our criteria for an "active" vendor is:
1. Status is "A"
2. Last PO activity on/after 1/1/2005 or Vendor record was created on/after 1/1/2006. (The "created on/after" criteria is to catch vendor records recently created but no activity yet).

I know the basics, using two queries:
a) created a Totals query to capture the last PODATE for each vendor.

b) query to feed report:
Fld STATUS MaxPODATE ENTDATE
Tbl VENDOR qryLastPO VENDOR
Cri "A" >= "1/1/2005" >= "1/1/2006"

Question #1
How do I "position" (which criteria rows) the 3 criteria to achieve the "A" and ( "B" or "C" )? I know they are not all on the first row.

Question #2
Can I create the SQL command(s) within the report object and eliminate the 2 queries? In effect, Select {fields} from ... where VENDOR.STATUS = "A" and ( {?}.MaxPODATE >= "1/1/2005" or VENDOR.ENTDATE >= "1/1/2006" ) ...
(I might as maximize taking my skills to a new level).

 

[Remou]

You can build your query into the report by clicking the three little dots to the right of Record Source on the report Property Sheet. If you put all your criteria on one line, you have And, if you put them on different lines, you have Or. You can also type this stuff into the SQL window.

[tt]Fld STATUS MaxPODATE ENTDATE
Tbl VENDOR qryLastPO VENDOR
Cri "A" >= "1/1/2005" >= "1/1/2006" This is And[/tt]

[tt]Fld STATUS MaxPODATE ENTDATE
Tbl VENDOR qryLastPO VENDOR
Cri "A"
>= "1/1/2005" >= "1/1/2006" This is Or[/tt]

Is this what you mean?

 

[EdAROC]

Thanks for the instructions about placing the SQL statement into the Record Source.

About the query, design view, reply:
I would interpret it to read ... Where STATUS = "A" or ( MaxPODATE >= "1/1/2005" and ENTDATE >= "1/1/2006" )

What's needed is:
... Where STATUS = "A" and ( MaxPODATE >= "1/1/2005" or ENTDATE >= "1/1/2006" )

Am I interpreting your reply incorrectly?

 

[straybullet]

try this
Fld[tab][tab]STATUS[tab][tab]MaxPODATE[tab][tab]ENTDATE
Tbl[tab][tab]VENDOR[tab][tab]qryLastPO[tab][tab]VENDOR
Cri[tab][tab]"A"[tab][tab]>= "1/1/2005"
Cri[tab][tab]"A"[tab][tab][tab][tab][tab][tab][tab][tab]>= "1/1/2006"



Let them hate - so long as they fear... Lucius Accius

 

[Remou]

That will be:
[tt]Fld STATUS MaxPODATE ENTDATE
Tbl VENDOR qryLastPO VENDOR
Cri "A" >= "1/1/2006"
Cri "A" >= "1/1/2005"[/tt]

 

[EdAROC]

Duh!!! (To myself)
This would be an application of something we learned in algebra - the law of distribution. Now I understand how to read the "picture" - how to interpret the design view in the grid.
Remou's is the solution I needed - Thanks Remou.

Thank you all for your replies.

The report works great! We have taken it from printing all the active (flagged as active) vendors on file (1,883) down to truely active vendors ... 433. That's 49 sheets NOT printed, at 30 labels per sheet. More importantly, the A/P accountant doesn't have to stop and think about which labels to use.