By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 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
Share this Question
Share on Google+
7 Replies


P: n/a
TC
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" <mt*****@earthlink.net> 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" <a@b.c.d> 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" <mt*****@earthlink.net> 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 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

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
TC
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" <mt*****@earthlink.net> 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" <a@b.c.d> 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" <mt*****@earthlink.net> 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" <mt*****@earthlink.net> 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" <mt*****@earthlink.net> 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" <mt*****@earthlink.net> 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.