Sort form by absolute value

[dima69]

Hi all.
Here is a problem. I want to sort a form by absolute value. Let's say, if I have a field named "theSum", I'd like to set the form OrderBy property to "Abs([theSum])". If I use "Advanced filtering\sorti ng" from "Records" menu, it works just fine, and OrderBy proprty becomes "Abs([theSum])". But when I do the same thing manually or programmaticall y, it doesn't work.
So I cannot figure out what is the trick here and how to make this work.

[Rabbit]

What's the code you're using?

[MMcCarthy]

If you set the order by in the query behind the form it should work.

[dima69]

What's the code you're using?

The code is very simple:

Me.OrderBy = "Abs([theSum])"
Me.OrderByOn = TRUE

[dima69]

If you set the order by in the query behind the form it should work.

Yes, it would be the workaround, with some drawbacks. However, I'me looking for more elegant solution. I know it exists - the fact is that using that menu does "something" to make it work. The question is - what it that "something" ?

[MMcCarthy]

Just to ask the obvious. Are you sure the textbox name corresponding to this field is "theSum"

[dima69]

Just to ask the obvious. Are you sure the textbox name corresponding to this field is "theSum"

By chance, It is. But it does not have to be, since the OrderBy property referes to the underlying field name, am I wrong ?

[MMcCarthy]

By chance, It is. But it does not have to be, since the OrderBy property referes to the underlying field name, am I wrong ?

No, I just don't always trust Access and like to think outside the box occasionally. :)

[MMcCarthy]

At a guess I would say this boils down to the order by which Access triggers events. I'm going to ask some of the other experts for their opinion.

Mary

[Rabbit]

The advanced filter/sort works because it is changing the underlying record source. As demonstrated by the fact that a Query Design window comes up and that you can load a query as the filter/sort. So to do it through code you'll have to change the record source of the form.

I don't see how this is any more elegant than using a query that's already sorted as the record source in the first place.

[Denburt]

Have you tried using abs in your control source or in the query itself? Then all values are absolutes and in the orderBy you put the field name itself?

Me.OrderBy = [theSum]
Me.OrderByOn = TRUE

It should work as such unless I am missing something.

[dima69]

The advanced filter/sort works because it is changing the underlying record source.

That seems reasonable, but the RecordSource property of the form does not change after applying the advanced filter/sort. So it must be something else.

[dima69]

Have you tried using abs in your control source or in the query itself? Then all values are absolutes and in the orderBy you put the field name itself?

Me.OrderBy = [theSum]
Me.OrderByOn = TRUE

It should work as such unless I am missing something.

What I am trying to accomplish here is not just to allow one kind of sort on one form.
I have a custom "Advanced Sort Utility" form, which can apply on any data form in the application. I'm trying to add an ability of sorting by absolute values to that utility, so it must work in universal way, on any given form.

[Rabbit]

Well then what about building a SQL statement in code that will become the form's recordsource?

[nico5038]

Then why not add the ABS() value to the form's recordsource and use that in the "normal" way.

Nic;o)