The number of row in the query

[someboddy]

Is there a way to return the number of row in a query as a query function?

For example, if I have a table:
George
John
Dave


I want a query that returns:
1|George
2|John
3|Dave

If I sort it:
1|Dave
2|George
3|John

If I filter John out:
1|George
2|Dave

And so on. No matter what I do, the first field should return the number of row in the query.

Can it be done?

 

[apr pillai]

I don't think it is possible directly on the Query in the form of an expression. If you are only interested in the final result then there can be an alternative solution and it is conditional.

1. The Query must be an updatable one.
2. The Source Table should have a dedicated numeric field to insert the Sequence Number and add it to the Query as a Column.
3. After the Data is filtered with whatever condition you set, run the following VBA procedure to renumber the Records, in the order in which they are sorted.

Change the Query Name and field Name to match with the names you have created.

Public Function AddSequence() As Long
Dim db As Database, rst As Recordset
Dim k As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("Query23")
k = 0
Do While Not rst.EOF
k = k + 1
With rst
    .Edit
    ![Seq] = k
    .Update
    .MoveNext
End With
Loop

rst.Close

Set rst = Nothing
Set db = Nothing

End Function

 

[MStef]

Yes, (apr pillai), it can be done via function. More easyer.
Look at attachment, zip
Look at module1, Form1, Query (last column).
Open Form1 and try.

 

[someboddy]

Thanks for the idea MStef, but is there a way to reset the function from inside the query?

Basically, I need to link to that query as a field from a Word document, so I can't call the reset function from a form.

 

[gemma-the-husky]

generally, when you think you want something like this, you dont really. What are you doing that makes you think you want a row number?

 

[someboddy]

Well, I want to use a database field in a word document to create an updateable phone list from a database. I need that list numbered, so for that I need a field in the query that represents the number of row.

 

[gemma-the-husky]

i thought it would be something like that

if you just want a number on the PRINTED list, just use running totals or something similar when you actually PRINT it - so you get a sequential count.

if you want a permanent number, then add it to the table as a field.

If you put it in the query, then it is meaningless, as it will change each time you re-order the query, or add a new number.

 

[Brianwarnock]

If you put it in the query, then it is meaningless, as it will change each time you re-order the query, or add a new number.

I was under the impression from post 1 that that is what he wanted.

Brian

 

[someboddy]

I was under the impression from post 1 that that is what he wanted.

Brian

Yup.
Can it be done?

 

[someboddy]

Sorry for the trouble guys, but it turned out that I didn't need it after all. All I had to do is add a blank field to the query, and use Word to turn it into a numbered list.
Anyways, I think I'll still add a number field to the table, to control the order.