[AccessD] Generating a rownumber field in a query

Hi Bruce

Here's a function for this purpose:

Public Function SequentialID( _
  ByVal booReset As Boolean, _
  Optional ByVal varDummy, _
  Optional ByVal intIncrement As Integer = 1, _
  Optional ByVal lngInitialID As Long) _
  As Long
  
  ' Increments static variable lngCurrentID with intIncrement.
  ' Returns the new value of lngCurrentID.
  ' Parameter varDummy is used to force repeated calls of
  ' this function when used in a query.
  '
  ' Reset to start counting from zero incrementing by one:
  '   Call SequentialID(True)
  ' Reset to start counting from 1000:
  '   Call SequentialID(True, Null, 1, 1000)
  ' Reset to start counting from zero incrementing by 2:
  '   Call SequentialID(True, Null, 2)
  ' Reset to start counting from -2000 incrementing by -8
  ' and returning initial ID:
  '   lngID = SequentialID(True, Null, -8, -2000)
  '
  ' Retrieve the current ID:
  '   lngID = SequentialID(False)
  ' Do a count by one and retrieve the current ID:
  '   lngID = SequentialID(False, Null, 1)
  ' Do a count by one in a query and retrieve the current ID:
  '   lngID = SequentialID(False, [fldAnyField], 1)
  ' Do a count by minus two and retrieve the current ID:
  '   lngID = SequentialID(False, varAny, -2)
  '
  ' 2001-12-13. Cactus Data ApS, CPH.
  
  Static lngCurrentID As Long
  Dim intSgn          As Integer
  
  If booReset = True Then
    ' Reset ID.
    lngCurrentID = lngInitialID
  ElseIf Not intIncrement = 0 Then
    intSgn = Sgn(intIncrement)
    If intSgn * lngCurrentID < intSgn * lngInitialID Then
      ' Reset ID.
      lngCurrentID = lngInitialID
    Else
      ' Increment ID.
      lngCurrentID = lngCurrentID + intIncrement
    End If
  End If
  
  SequentialID = lngCurrentID
  
End Function

/gustav

>>> Bruce.Bruen at railcorp.nsw.gov.au 06/10 4:08 am >>>
Hi folks,
 
I want to output a sequence number as a field in a query result.
Everytime the query runs it should restart the sequence number from 1.
 
The query appends rows to a "testscript" table, every new test cycle
we
generate a new set of test scripts which is made up of all the tests
in
the previous cycle plus new tests that were added during the execution
of the last cycle.  The natural key for the output table is the cycle
number + the sequence number.
 
Cycle    Seq    Testname .....   
   1          1        blah
   1          2        blah blah
...
   2          1        blah blah
   2          2        blah blah blah
etc
 
The cycle number is input as a parameter to the append query.  But how
can I generate the sequence numbers?