How do I get the record number from a select

[rambleon]

Hi, is it possible to get the record number of a record returned from a select statment (only one record will be retuned)
Thanks for any help

Rambleon

 

[lespaul]

What do you mean, "record number"?

Leslie

 

[rambleon]

By "record number" I mean the position of the record in the recordset. I want to use the result for the Offset value of a GoToRecord statement

 

[lespaul]

Well, if only one record is returned, where do you want to position it?

Leslie

 

[rambleon]

I want to identify the position of a record within a recordset. When I say 1 record will be returned I mean I'm selecting a unique value from the recordset and I want to know the position of that record within the record set.
What I'm looking for is the Recordset.AbsolutePosition and I was wandering if there's a simple select or agregate function that would return this value.

 

[Golom]

No there isn't. Until you actually retrieve the records into a recordset there is no ordering in the "record number" sense. Relational databases do not guarantee any particular ordering of records without an "ORDER BY" clause and they don't use record numbers.

A recordset imposes a numbering via AbsolutePosition but that's unknown to the RDBMS or to SQL.

 

[PHV]

Doesn't has your "recordset" a Primary Key, or at least an unique index ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rambleon]

Golom I'm selecting the record from a recordset every record has a fixed position in that recordset on a form based on the recordset I'm selecting my record from there are navigation buttons and a record number box all I want to know is that where field x = y the record number is z.

 

[PHV]

Take a look at RecordsetClone and FindFirst.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244