rownum(oracle) equivalent

  • Thread starter Thread starter Tanuj Ralhan
  • Start date Start date
T

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.
 
I want to use rownum(oracle) like function to return a
running value like serial no. in Access Query. Any help
please.
Click to expand...

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.
 
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.
 
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.
 
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.
 
Back
Top