Function to number lines in a query

[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

 

[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]

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

 

[Melissa]

TC,

Thank you for your response!

Could you show me how to declare NN as a global variable.

Thanks,

Melissa

 

[Marshall Barton]

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

 

[Stephen Lebans]

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.

 

[almish]

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