rownum(oracle) equivalent

[Tanuj Ralhan]

I want to use rownum(oracle) like function to return a
running value like serial no. in Access Query. Any help
please.

Thanks in advance.

 

[John Vinson]

I want to use rownum(oracle) like function to return a
running value like serial no. in Access Query. Any help
please.

No, because Access tables do not have Rownums. There simply is no such
construct.

You'll need to use a Subquery to count the number of records before
the current record. If you need line numbers on a Report, you can set
the Control Source of a textbox to 1 and set its RunningSum property
to True; if you want a sequential Custom Counter you can use VBA code
in a Form's BeforeInsert event to find the largest current value and
increment by 1 - but there is no Rownum.

 

[Albert D. Kallal]

If you have a report, you can simply place a unbound text box with a control
source of:

=(1)

Then, just set the running sum property of the text box to yes. It will thus
give you a rownum count.

 

[Tanuj Ralhan]

Thanks Albert for reply. Actually I have a grid where the
records are displayed as per some criteria and I want to
show serial no. along with the selected records. I am able
to do it programmitically by iterating the recordset. I
was wondering if it could be done by the query itself so
that I save on performance as well as the code like I used
to do it in oracle queries.

 

[Albert D. Kallal]

For grids on the screen, I use:


Function Rpos(vId As Variant) As Long

Rpos = 0
If IsNull(vId) = False Then
Me.RecordsetClone.FindFirst "id = " & vId
If Me.RecordsetClone.NoMatch = False Then
Rpos = Me.RecordsetClone.AbsolutePosition + 1
End If
End If

End Function

Then, you can put a un-bound text box in the continoues form,and

=(rpos([id]))

The above assumes you have a key field called id.


It also assumes dao.

 

[Zeppo]

Maybe they found it to hard to implement:~).