numbering line entries

[tgavin]

I need to export a query to excel with PO and their line items. Each line item of a PO needs to be numbered, starting with 1000, then 2000, etc for each item of that PO. If the export contains 3 POs there would be 3 sets of line items starting with 1000. I am not worried about the 0s, just how to count per PO. Any one have any ideas on how do I do this?

Thanks
Terri

[pbaldy]

Presuming there's an ID field you can use to sort, you either use DCount() or a subquery that counts all records with an ID field less than or equal to the current record, with a further criteria on PO. That gets you 1, 2, 3. From there you just multiply by 1000.

[tgavin]

Paul, I am doing something wrong. I have 2 different POs, one with 2 items, 1 with 1.

Here is my formula
PDLNID: DCount("[PurchaseID]","qryLineCount","[intPO]=[intPO]")

Here is what it returns
qryPODetailExportPO#PDDCTOPDSFXOPurchaseIDPDLNID70675OD0003036370675OD0003252370705OD00032673
Here is what I need it to return
PO#PDDCTOPDSFXOPurchaseIDPDLNID70675OD0003036170675OD0003252270705OD00032671

[tgavin]

That is not reading right
PO# (space)
PDDCTO (space)
PDSFXO (space)
PurchaseID (space)
PDLNID (new line)
70675 (space)
OD (space)
000 (space)
3 or 1 (new line)
70675 (space)
OD (space)
000 (space)
3 or 2 (new line)
70705 (space)
OD (space)
000 (space)
3 or 1 (new line)

Sorry about that.

[pbaldy]

Is PurchaseID a unique value in that table? Like I said, you also need the second criteria, not just PO number. If that's a unique value, try

DCount("*","qryLineCount","[intPO]=" & [intPO] & " AND PurchaseID <=" & PurchaseID)

[tgavin]

Yes!!!!

Thank you so much.

[pbaldy]

No problemo!