DMAX with Criteria

[Tom Ventouris]

Is there a way to add criteria to the expression that creates my serial
numbers?

Me![TxID] = Format(DMax("[TxID]", "[tblMyTable]") + 1, "0000")

I want the next number in the series to be based on a category field on the
form. which will be matched to a ctegory field in MyTable.

In other words:
The user allocates the record to a Category and clicks a button to return
the next available number in the series for that category.
This will result in duplicate numbers in the Seriel Number Field, but this
is not my Primary Key, (I am using an Autonumber Field for this.

The first numbers for each categeroy will be created manually for the
incremental numbers to start.

 

[XPS350]

Is there a way to add criteria to the expression that creates my serial
numbers?

Me![TxID] = Format(DMax("[TxID]", "[tblMyTable]") + 1, "0000")

I want the next number in the series to be based on a category  field on the
form. which will be matched to a ctegory field in MyTable.

In other words:
The user allocates the record to a Category and clicks a button to return
the next available number in the series for that category.
This will result in duplicate numbers in the Seriel Number Field, but this
is not my Primary Key, (I am using an Autonumber Field for this.

The first numbers for each categeroy will be created manually for the
incremental numbers to start.

If Category is a text field:
Me![TxID] = Format(DMax("[TxID]", "[tblMyTable]") + 1,
"0000","Category='" & Me.Category & "'")

If it is an number:
Me![TxID] = Format(DMax("[TxID]", "[tblMyTable]") + 1,
"0000","Category=" & Me.Category)

Groeten,

Peter
http://access.xps350.com