Incremental invoice numbers from previous records

[martlewis]

How do i take a value from a field in the previous record and increment it then put it to the same field in the current record?

Cheers.

 

[jdraw]

Allen Browne has several tips for Access. Here is one about subqueries
http://allenbrowne.com/subquery-01.html

see the example of Get the value in another record

You can also use DMax()

eg dmax("FieldName", "TableName") + 1

see http://www.techonthenet.com/access/functions/domain/dmax.php

 

[martlewis]

Dmax worked perfectly as every year I start the invoice numbers with the year (eg 20110001, 20110002, etc.) I can change the first invoice number of the year and the rest of the year now carries on from there.

Thank you for that.

 

[CanWest]

You can also use DMax()

eg dmax("FieldName", "TableName") + 1

Would you know how to make that loop through a set of records in a query to place a value that was increased by one in every record

 

[fleurette]

Allen Browne has several tips for Access. Here is one about subqueries


see the example of Get the value in another record

You can also use DMax()

eg dmax("FieldName", "TableName") + 1

I tried using the above e.g. in my situation.
Expr1: DMax([Serial Number],[Policy Information])+1

but I am getting an error message.

Serial number has data like, 0001, 0002, 0003
and policy information is my table.

any one can help with what's wrong with the syntax?

thanks