Get record number of longest record

[Swi]

I have the following code that retrieves the longest name from a table:

        rs.Open "SELECT [NAME] FROM [TESTIT] WHERE LEN(" & [NAME] & ") = (SELECT MAXLEN([NAME])) FROM [TESTIT])", conn, adOpenForwardOnly, adLockReadOnly, adCmdText

Any ideas on how to modify this statement to get the record number in the resulting query? Thanks.

Swi

 

[lespaul]

what do you mean "record number"? like an ID field that's in the table?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Swi]

AcSorry, I should have been more specific. I would like the number as to where the record falls in the table. So in Access in row 50 the name is John Sample. I would like to return the number 50 along with the name.

Swi

 

[lespaul]

how do you know that John Sample is record 50?

 

[Swi]

I get your point. Then the only way to know what record it is is to have a identifier on the record.

Swi

 

[lespaul]

you can search the forum for 'ranking' query and you can return a result set with a query "record number" but that's just within the query results...maybe that will work for you?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Swi]

Thanks but I do not think that will accomplish what I want to do. I want to return the longest name but know where it fell in the entire file.

Ex. -

John Doe
Jane Doe
Cousin Doe
Baby Doe

Cousin Doe is the longest record and I want to know that it was the 3rd record in the table I was querying.

Swi

 

[Swi]

Can someone show me how to impliment ranking into my above query?

Swi

 

[dhookom]

There is no "3rd" record in a table. Records in a table are like marbles in a bag or children on a playground. There is no order.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Swi]

Sorry, I am used to working with text files and not databases.

Swi

 

[dhookom]

I remember working with dBASE files where there were record numbers. When I moved to Access, I couldn't understand how I would ever be able to use a database without record numbers. Now, I don't know why I ever thought I needed them.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Swi]

So you can accomplish what I want to do with dBASE files? If so, please explain how as I would like to do that as well. Thanks.

Swi

 

[GingerR]

Tell us why you need a record number, maybe we can provide a good solution for you. Is it that then you want to go find the record and display it? Why do you need to know it?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Swi]

We would like to know the record number because after this report is ran we want to know where it falls in the DBF so we can go to the exact record number without searching later on if needed.

Swi

 

[dhookom]

You don't need a record number. You need the primary key value of the record. This will allow you to accurately find the record in the "bag of records".

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[PHV]

we want to know where it falls in the DBF
Instead of record number use PrimaryKey ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

There is no primary key that I am aware of.

Swi

 

[PHV]

There is no primary key
So, how do you retrieve a peculiar row ???

Furthermore, why posting in the JetSQL forum for a DBF issue ?

 

[Swi]

This was not necessarily a DBF issue to start with but it was mentioned in one of the posts above and I thought I would keep it in one thread. I will try another forum. Thanks.

Swi

 

[GingerR]

huh?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html