Sorting on the third character in name field...

[darno]

Hi Folks,

I have a report which has employee's names with auto generated serial numbers. The Names in the list are like this:

Y.Yong
C.Lee
Armstrong
A.Baker
A.Andrew
B.David
Alaxander
David
T.Philips
C.David
Z.Bony

I would like to sort the above list to the following format:

A.Andrew
Alaxander
A.Baker
Z.Bony
C.David
B.David
C.Lee
T.Philips
Y.Yong

if u look at the above desired list. the sort is on the 3rd character in the whole name. for example alaxander comes 2nd in the list because 3rd characrter in his name is A. then comes A.baker and list continues based on 3rd character sort.

Please help me to achieve the above result.

 

[neileg]

In the query that the report is based on add a calculated field that uses Left() to extract the surname. Sort on that.

 

[boblarson]

In the query that underlies the report, create a field like this and set it to ascending:

SortLetter:Mid([YourNameFieldHere],3)

 

[boblarson]

In the query that the report is based on add a calculated field that uses Left() to extract the surname. Sort on that.

Not LEFT, use MID starting at the 3rd character.

 

[neileg]

Not LEFT, use MID starting at the 3rd character.

I would use Left() and Len().

You say patayto and I say potarto!

In reality you really need the whole surname and the initial in separate feilds to sort on, otherwise there'd be no consitency after the sort. I mean that you wouldn't know if Jones would sort before or after James.

 

[boblarson]

You don't want left as he is wanting to sort on the 3rd letter, so why would you pull the characters in front of it? You don't want the characters in front of it. That's why you want MID so that you can start the sort with the characters AFTER the first two characters. But, after reading it further, it will require an INSTR function too to find the location after the period, if there is one and use the first character if there isn't.

 

[neileg]

You don't want left as he is wanting to sort on the 3rd letter, so why would you pull the characters in front of it? You don't want the characters in front of it. That's why you want MID so that you can start the sort with the characters AFTER the first two characters. But, after reading it further, it will require an INSTR function too to find the location after the period, if there is one and use the first character if there isn't.

The explanation is really simple. I'm stupid! I meant Right().

 

[boblarson]

This is a function that would work in a query (I tested it):

MyNewFieldName: IIf(InStr([YourFieldNameHere],".")>0,Mid([YourFieldNameHere],InStr([YourFieldNameHere],".")+1),[YourFieldNameHere])

 

[darno]

Thanks but one more step...

This is a function that would work in a query (I tested it):

MyNewFieldName: IIf(InStr([YourFieldNameHere],".")>0,Mid([YourFieldNameHere],InStr([YourFieldNameHere],".")+1),[YourFieldNameHere])

Hi bob,

It worked again as usual, but there is one more step i need. I wish to print the whole name with dot also: for example your query prints Alaxander but not A.Alaxander i wish to see the names in the same manner as they were but sorted on 3rd character

Thanks for all time gr8 help

Regards

Darno

A.Alaxander

 

[boblarson]

You only create a field to sort on, but you display the regular field.

 

[darno]

Thanks...

You only create a field to sort on, but you display the regular field.

hi bob,

it worked again. Thanks a lot man.

Regards


darno