Hi,
With Jet, you can't, safely.
A possible trick may work in some cases (if you do not order by on that
field and if you forward only in the recordset). Make a VBA function like:
Public Function MyCounter( Optional x As Variant) As Long
Static i as long
If IsMissing(x) then
i = 0
Else
i=i+1
End If
MyCounter=i
End Function
And use:
SELECT MyCounter( ), MyCounter( SomeFieldNameHere) As Counting, ....
FROM ...
The first MyCounter( ), without argument, will initialize the static
variable i to zero. Since it does not imply a field name, it would be
called just once, at the beginning of the query. The second MyCounter will
be called for each record, thus supplying a continuous sequence of
integers... The problem is that Jet computes the VBA expression "on
request", so, if you move backward, the second MyCounter will be called
again, supplying another number and broking, eventually, the sequence. So,
this trick can be used only in some controlled circumstances.
Hoping it may help,
Vanderghast, Access MVP
