×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL > FAQs

Microsoft: Access Queries and JET SQL FAQ

getting an incremental value

How do you really put an incremental value in your query? by euskadi
faq701-1155
Posted: 10 Oct 01

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.

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close