Thanks to IonFilipski for getting this started. I couldn't get his function to work right for me, so I made an update:
global IncrementVariable as Long
function IncrementValues(i) as Long IncrementVariable = IncrementVariable + 1 IncrementValues = IncrementVariable end function
Note that it's necessary to pass a value to the function in order to have it count for each record. If you don't include this piece then you'll likely get a lot of 1's in every row instead of the counting result you'd like to see.
SELECT ... IncrementValues([any_field]) ...
You can futher customize this function, the following code with start over at 1 every 4 seconds, so if you run a query that numbers your table, it will start over at 1 on it's own next time you run the query (assuming it's > 4 seconds later).
global IncrementVariable as Long global lastcall as Date
Function IncrementValues(i) As Long If Now > (lastcall + 4 / 60 / 60 / 24) Then lastcall = Now IncrementVariable = 0 End If IncrementVariable = IncrementVariable + 1 IncrementValues = IncrementVariable End Function
And this one restarts every 4 seconds, and gives you the option of designating a starting point other than 1.
Function IncrementValues(i, Optional myBase As Long) As Integer If Now > (lastcall + 2 / 60 / 60 / 24) Then lastcall = Now If myBase Then IncrementVariable = myBase - 1 Else IncrementVariable = 0 End If End If IncrementVariable = IncrementVariable + 1 IncrementValues = IncrementVariable End Function
Finally, it's a good idea to choose data type Long instead of Integer, in case you have a lot of data... and if you're running on a P75, you might want to omit the timed restart, or do more than a 4 second delay, or it might restart at 1 in the middle of running a long slow query.