dan-cat
Registered User.
- Local time
- Today, 07:46
- Joined
- Jun 2, 2002
- Messages
- 3,433
Hope you can help... I am trying to create an append query which will paste selected records from one table(x) to another table. No problem there... however I have a field called "InvoiceNo" in both tables. I want to be able to update the field "InvoiceNo" in table Y with sequential numbers running on from the last Invoice Number in table X.
ie. if the last Invoice number in table X is 1001 and the query selects 5 records to past into table Y Im looking to update the Invoice No for these 5 records in table Y as 1002,1003,1004,1005 and 1006 in turn. Here is what I have done so far:
INV: nz(DMax("InvoiceNo","tblX","Status='Invoiced'")+1,1001) - append to InvoiceNo in tblY.
This manages to update all the fields in tableY with the next invoice number - ie all 5 records will show 1002 as the InvoiceNo but Im looking for the InvoiceNo to scroll on one for each record.
Is this possible at all?
Thanks
Dan
ie. if the last Invoice number in table X is 1001 and the query selects 5 records to past into table Y Im looking to update the Invoice No for these 5 records in table Y as 1002,1003,1004,1005 and 1006 in turn. Here is what I have done so far:
INV: nz(DMax("InvoiceNo","tblX","Status='Invoiced'")+1,1001) - append to InvoiceNo in tblY.
This manages to update all the fields in tableY with the next invoice number - ie all 5 records will show 1002 as the InvoiceNo but Im looking for the InvoiceNo to scroll on one for each record.
Is this possible at all?
Thanks
Dan