Auto Number in Query

[Alex Motilal]

Hello Everybody,
In my sales Data Base, there is a table for payments received Invoice vice. For a particular Invoice, payments are received in different dates. In the payments form, when a Invoice is selected, the Subform displays the payments table of the Invoice selected. What I want to know is that, is it possible to create a field which displays the Serial Numbers in the Ascending Order of the dates of that particular Invoice?
Eg. For Invoice Number 001, payments received on 09/01/2009, 09/02/2009 & 09/03/2009, the numbers should be 1,2 & 3.
I request somebody to help.
Thanks.

[dcrake]

You can go down two routes

1. Create the sequential number at point of input and store the number in a field.

2. use a running sum function that you need to create your self.

The second option will slow down your system but aleviates the need for an extra field.

Create function call rSum. See below. Save it in a standard module

 
Function rSum(AnyPK As Long, AnyTable As String, AnyField As String) As Long
    rSum = DCount("*", AnyTable, AnyField & "< " & AnyPK)
End Function

Then in your query

nIndex: rSum([FieldToCount],"QueryorTable","Field")