448,930 Members | 1,699 Online
Need help? Post your question and get tips & solutions from a community of 448,930 IT Pros & Developers. It's quick & easy.

# Function to number lines in a query

 P: n/a I'm trying to create a function that I can put in a query field that will consecutively number the records returned by the query starting at 1 and will start at 1 each time the query is run. So far I have the function shown below which doesn't work. If Reset is True then all I get is 1 in every field and if Reset is False, the numbering does not start at 1 each time the query is run. Can someone show me a function that works. Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As Integer Static NN As Integer If Reset = True Then NN = 0 NN = NN + 1 NextNumber = NN End Function Thanks! Melissa Nov 12 '05 #1
7 Replies

 P: n/a One way would be to discard the Reset parameer, and declare NN as a global variable (not a local static). Then you could reset NN from code, before you run the query: NN = 0 docmd.openquery ... HTH, TC "Melissa" wrote in message news:%9*******************@newsread1.news.atl.eart hlink.net... I'm trying to create a function that I can put in a query field that will consecutively number the records returned by the query starting at 1 and will start at 1 each time the query is run. So far I have the function shown below which doesn't work. If Reset is True then all I get is 1 in every field and if Reset is False, the numbering does not start at 1 each time the query is run. Can someone show me a function that works. Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As Integer Static NN As Integer If Reset = True Then NN = 0 NN = NN + 1 NextNumber = NN End Function Thanks! Melissa Nov 12 '05 #2

 P: n/a TC, Thank you for your response! Could you show me how to declare NN as a global variable. Thanks, Melissa "TC" wrote in message news:1069733055.699253@teuthos... One way would be to discard the Reset parameer, and declare NN as a global variable (not a local static). Then you could reset NN from code, before you run the query: NN = 0 docmd.openquery ... HTH, TC "Melissa" wrote in message news:%9*******************@newsread1.news.atl.eart hlink.net... I'm trying to create a function that I can put in a query field that will consecutively number the records returned by the query starting at 1 and will start at 1 each time the query is run. So far I have the function shown below which doesn't work. If Reset is True then all I get is 1 in every field and if Reset is False, the numbering does not start at 1 each time the query is run. Can someone show me a function that works. Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As Integer Static NN As Integer If Reset = True Then NN = 0 NN = NN + 1 NextNumber = NN End Function Thanks! Melissa Nov 12 '05 #3

 P: n/a Melissa wrote: I'm trying to create a function that I can put in a query field that willconsecutively number the records returned by the query starting at 1 and willstart at 1 each time the query is run. So far I have the function shown belowwhich doesn't work. If Reset is True then all I get is 1 in every field and ifReset is False, the numbering does not start at 1 each time the query is run.Can someone show me a function that works.Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As IntegerStatic NN As IntegerIf Reset = True Then NN = 0NN = NN + 1NextNumber = NNEnd Function That approach will not work Melissa. Access retrieves records from a query on an as needed basis. Even after you sort out the immediate problems, when you open the query in data sheet mode you'll see the numbers looking great until you scroll forward a ways then scroll backwards. The records scrolling in on the top of the sheet will continue to count up from the highest number displayed when you were scrolling forward. To make a query display sequential numbers, you need to do it entirely in the query by using a subquery. One of the keys to this kind of operation is that you have to have a well defined sort order with no duplicate values in the order by field. Here's an example (air code) SELECT table.f1, table.f2, table.f3, (SELECT Count(*) FROM table As X WHERE X.sortfield <= table.sortfield ) As RecNum FROM table ORDER BY sortfield -- Marsh MVP [MS Access] Nov 12 '05 #4

 P: n/a Just go to the top of the module & enter: Public NN as integer Voila! But naturally, it would be better to name it something better. HTH, TC (off for the day) "Melissa" wrote in message news:UL******************@newsread1.news.atl.earth link.net... TC, Thank you for your response! Could you show me how to declare NN as a global variable. Thanks, Melissa "TC" wrote in message news:1069733055.699253@teuthos... One way would be to discard the Reset parameer, and declare NN as a global variable (not a local static). Then you could reset NN from code, before you run the query: NN = 0 docmd.openquery ... HTH, TC "Melissa" wrote in message news:%9*******************@newsread1.news.atl.eart hlink.net... I'm trying to create a function that I can put in a query field that will consecutively number the records returned by the query starting at 1 and will start at 1 each time the query is run. So far I have the function shown below which doesn't work. If Reset is True then all I get is 1 in every field and if Reset is False, the numbering does not start at 1 each time the query is run. Can someone show me a function that works. Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As Integer Static NN As Integer If Reset = True Then NN = 0 NN = NN + 1 NextNumber = NN End Function Thanks! Melissa Nov 12 '05 #5

 P: n/a Here's a ready made solution: http://www.lebans.com/rownumber.htm Rownumber.zip is a database containing functions for the automatic row numbering of Forms, SubForms and Queries. Updated Oct. 13 by Allen Browne. Includes error handling and cleaned code. -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can benefit. "Melissa" wrote in message news:%9*******************@newsread1.news.atl.eart hlink.net... I'm trying to create a function that I can put in a query field that will consecutively number the records returned by the query starting at 1 and will start at 1 each time the query is run. So far I have the function shown below which doesn't work. If Reset is True then all I get is 1 in every field and if Reset is False, the numbering does not start at 1 each time the query is run. Can someone show me a function that works. Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As Integer Static NN As Integer If Reset = True Then NN = 0 NN = NN + 1 NextNumber = NN End Function Thanks! Melissa Nov 12 '05 #6

 P: n/a Have a look here: http://www.lebans.com/rownumber.htm Rownumber.zip is a database containing functions for the automatic row numbering of Forms, SubForms and Queries. Updated Oct. 13 by Allen Browne. Includes error handling and cleaned code. -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can benefit. "Melissa" wrote in message news:%9*******************@newsread1.news.atl.eart hlink.net... I'm trying to create a function that I can put in a query field that will consecutively number the records returned by the query starting at 1 and will start at 1 each time the query is run. So far I have the function shown below which doesn't work. If Reset is True then all I get is 1 in every field and if Reset is False, the numbering does not start at 1 each time the query is run. Can someone show me a function that works. Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As Integer Static NN As Integer If Reset = True Then NN = 0 NN = NN + 1 NextNumber = NN End Function Thanks! Melissa Nov 12 '05 #7

 P: n/a "Melissa" wrote in message news:<%9*******************@newsread1.news.atl.ear thlink.net>... I'm trying to create a function that I can put in a query field that will consecutively number the records returned by the query starting at 1 and will start at 1 each time the query is run. So far I have the function shown below which doesn't work. If Reset is True then all I get is 1 in every field and if Reset is False, the numbering does not start at 1 each time the query is run. Can someone show me a function that works. Function NextNumber(SomeArgument, Optional Reset As Boolean = False) As Integer Static NN As Integer If Reset = True Then NN = 0 NN = NN + 1 NextNumber = NN End Function Thanks! Can you tell us more about your needs? If you have an AutoNumber field or Date/Time field you could "rank" the rows based on that - or do you have some other field that can be used to rank the query results? You can use a correlated subquery to count the number of records outside your current position, something like: SELECT field1 , field2 , (SELECT COUNT(*) FROM yourTable WHERE field1 > y.field1) + 1 As Ranking FROM yourTable y Nov 12 '05 #8

### This discussion thread is closed

Replies have been disabled for this discussion.