DMAX with Criteria

T

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.
 
X

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top