sorting a string

[trialerror]

Is it possible to sort a string field on the middle 2 digits? If a string has 6 digits , can it be sorted on the basis of the middle 2 digits?

 

[JediBMC]

Hi trialerror,

Yes, you can sort by the middle 2 digits. If you are doing this in a query, you will need to create a new field that will only include the middle 2 digits that you will sort by. Here is an example:

I want to use the middle 2 digits of field AccntNumb from the Customer_Info form. This field is a text field that is always 6 digits long.

In my query, I create a new field that looks like this,

Srt_AccntNumb: mid(Customer_Info.AccntNumb,3,2)

I then set the sorting to ascending.

If the field varies in length, you can find the middle 2 digits by dividing the length, retrieved by using the

len(fieldname)

function, by two and subtracting 1.

I hope this helps.