Increment Values in query

Reference:

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.