Can I create a field in a query which auto-increments?

It is possible using a slightly tricky Function call.

WARNING: Running functions which use STATIC variables (as this one does) within a query can cause strange effects if you scroll back and forth within the query results.

RECOMMENDATION: Use a "make table" query to extract the data into a separate table before you use it.


Public Function AutoIncr(SomeField As Variant, Optional StartFrom As Variant) As Integer

Static CurrentCounter As Integer

If IsMissing(StartFrom) Then
CurrentCounter = CurrentCounter + 1
ElseIf IsNumeric(StartFrom) Then
CurrentCounter = StartFrom
End If

AutoIncr = CurrentCounter

End Function


Access tries to be efficient when dealing with function calls in a query. When it sees an unchanging function, it will only call it ONCE, so we need to tell it to run the function for each row. We can do this by refering to some column in one of the underlying tables. For example, if you have a column called "EmployeeName"...

Add this to the query grid...

MyCounter: AutoIncr([EmployeeName])

You do not need to include the column you choose in the query itself and the AutoIncr function doesn't use it either.

You'll notice that if you run your query multiple times, that the number does NOT reset to 1.

To do that run the same query (or another setup for just this purpose):

MyCounter: AutoIncr("",0)