Index asc and Desc

Hi Everybody.

What technic you are using to combine in one index ascending and descending
orders?
I have two 12 Characters fields which I want to combine into one Index, But
I want first field to be in ascending order and second in descending order.

Thanks in advance.

Dave
Tue Apr 03 22:01:08 CDT 2007

If by chance one of the fields is a string of numbers (like a postal
code or part number) you can convert to a numeric, subtract it from a
large number and convert the result back to text.

index on field1+str(1000000000000-val(field2))

Leon wrote:
> Hi Everybody.
>
> What technic you are using to combine in one index ascending and descending
> orders?
> I have two 12 Characters fields which I want to combine into one Index, But
> I want first field to be in ascending order and second in descending order.
>
> Thanks in advance.
>
>

Gene
Tue Apr 03 23:30:24 CDT 2007

Leon <Leon@discussions.microsoft.com> wrote:

>What technic you are using to combine in one index ascending and descending
>orders?
>I have two 12 Characters fields which I want to combine into one Index, But
>I want first field to be in ascending order and second in descending order.

You do not have to either if you use SQL, but if you really want
an index that way, you could use chrtran():
first12+chrtran(second12,fromstring,tostring)
where fromstring consists of the characters that can occur in second12
in character set order and tostring consists of the same characters in
reverse order. Simplified example:
first12+chrtran(second12,"abcdefghij","jihgfedcba")
chrtran() can also be used to define a different order. If you think
it should be AaBbCc, etc., use
chrtran(changeme,"ABC . . . abc . . .","ACE . . .BDF . . .")

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.