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

 

[Swi]

I remember working with dBASE files where there were record numbers."

This is the quote I was referring to. I am querying access tables right now and I found out that I may have run this same type of query on DBF files in the future.

Swi

 

[dhookom]

Regardless of where the data is stored, your tables should have a primary key which defines some unique value for each record. This could be provided by one or more fields.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Swi]

Unfortunately this is not the case. This is why I am having such trouble. I wish I could set a rule to where every DBF has a unique field but they are coming from many different individuals.

Swi

 

[lespaul]

So you have many different individuals sending you information in some kind of format? text files? spreadsheets? How do you get this information?

What kind of information is it? Why it is important for you to know the order of the data? Why can't you take the information that is sent separately and create your own local tables that follow the rules of normalization, modify the incoming data to conform to those rules and then you can use the database as it was meant to be used?

Leslie

 

[Swi]

We have postal software that is utilized by 4 differenct programmers. They import raw data into the software that uses a DBF format. The data is sent in by hundreds of different clients so getting them to conform is a dream. I could however have the programmers add a unique ID on import.

Swi

 

[Swi]

I received an answer in the following thread:

thread709-1432409

Swi