DMAX with Criteria
in [General]
|
From: Tom Ventouris on 15 Apr 2010 02:12 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.
From: XPS350 on 15 Apr 2010 02:31 On Apr 15, 8:12 am, "Tom Ventouris" <t...(a)pmads.co.za> wrote: > 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
|
Pages: 1 Prev: Color Values in VBA Next: Is it true about DoCmd.SendObject |