5 Replies Latest reply: Aug 10, 2011 10:59 AM by stephenhuxley RSS

    Sort expression problem QV 8.5

      Hi could someone please help?

       

      I have a listbox which contains a list of Consultant names from a field called Consultants, e.g.

       

      DR A RASHID

      MR C TRUEMAN

      DR B MEHTI

      DR Z BIGELOW

       

      what I would like to do is sort this list on the surname so it would appear as

       

      DR Z BIGELOW

      DR B MEHTI

      DR A RASHID

      MR C TRUEMAN

       

      I have tried to add a sort expression like so

       

      =Mid([Consultant],FindOneOf([Consultant], ' ', 2))

       

      so this will find the surname after the second space in the string.  If I create a new list box with this expression in as the field it displays as I would expect and is sortable

       

      BIGELOW

      MEHTI

      RASHID

      TRUEMAN

       

      However this does not work if I use this to sort expression, I have tried all the different combinations of checkboxes to no avail

      I have tried to make a new field in the load script like so

       

      Mid([Consultant],FindOneOf([Consultant], ' ', 2)) as [Cons_Surname],

       

      The field is added and is available but I still cannot get it to sort correctly.

       

      I need to show the full string in the list box but for it to be sorted on the surname portion of the string

       

      Any ideas?

       

      Thanks a lot

      Stephen