Indexing with literals

[drisconsult]

Hello All

I have a field called MEMBERID

I wish to index the following so that all members in 2005 can be idexed A to Z and all members in 2006 can be indexed A to Z:

00125/05

At the moment, the last two digits that indicate the year of joining are ignored when sorted in a query, and they are all jumbled up. I have about two hundred antries for 2005, and 160 for 2006. What am I doing wrong?

Regards
Terence
London

 

[RuralGuy]

How about reversing the order and dropping the slash? 0500125

 

[gemma-the-husky]

Are you using a query to sort your members?

If so, add another column to your query called MemberYear: right(memberid,2)
then sort on this colum, and the name.

The index you are using will sort the memberid's but will sort from the left to the rightm which is not what you want. doing it this way will isolate the year part of the memberid for you.

 

[drisconsult]

Many thanks for your two replies

I have already tried both of these options and didn't like the results. Does this mean that it is not possible to index the query in it's present format.

Regards
Terence

 

[gemma-the-husky]

it is indexed, its just that you are using the string with the numbers you want at the end - thats like trying to sort the phone directory on the last letter of everybody's name. you have to find a way of getting to the year portion of the memberid - both these ideas work - one means having a different id, the other puts another column in your query - with respect, whats not to like?

 

[grnzbra]

With the leading zeros in place, shouldn't it sort as if it were numbers?

What is it that you are getting? Is it that you still want the xxxxx/yy format but would like to have them sort by year and number? If so, add two calculated fields to your query: YearSort:Right(MEMBERID,2) and NumSort:Left(MEMERID,5) Then sort with the year sorting first. Since you have leading zeros, I believe they will sort as numbers (0 in the hundreds column is lower than 1 in the hundreds column. Without the leading zeros, 1 in the tens column would sort with 1 in the hundreds column. The lead zeros keep them lined up properly.)