Sort form by absolute value

[Rabbit]

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

Nic;o)

Post #13 answers why.

[Killer42]

Post #13 answers why.

Yeah, you'd have to add an "absolute equivalent" for every numeric field in all your underlying queries, just in case the user decided to sort by it. Obviously this could be done, but it seems rather ugly.

[Denburt]

I think Killer has the quickest most efficient manner, if they want to sort by that field then change your controlsource and off you go. I have tried numerous scenarios...

[Killer42]

I think Killer has the quickest most efficient manner, if they want to sort by that field then change your controlsource and off you go. I have tried numerous scenarios...

Um... did I say that?

Actually, I was just agreeing with Rabbit that this would be difficult to set up because of the "universal" nature of the sort required. I guess technically it would work, but I still think it's a bit of an ugly solution.

Note that if the underlying query always has an absolute equivalent of each numeric field, then you would not need to change the recordsource - just set the OrderBy, as mentioned by the OP.

[NeoPa]

The code is very simple:

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

Going back to the start, before all the suggestions to do it other ways, this code should work. Not only should work, but in the scenario you describe, is the 'correct' way to approach the problem.
Have you tried running a .Refresh or .Requery on the form after changing these values?

[dima69]

Going back to the start, before all the suggestions to do it other ways, this code should work. Not only should work, but in the scenario you describe, is the 'correct' way to approach the problem.
Have you tried running a .Refresh or .Requery on the form after changing these values?

May be it should, but it does not :). Have you seen it work ?
What I see is after setting the OrderBy property, it just clears itself and remains empty - either by VB or directly typing into the property sheet, in form view.

[NeoPa]

I have to say my curiosity was piqued so I tried it out (After 02:30 so I might have been imagining the results) and it behaved exactly as you say. IE It did nothing (Even after a .Requery).
I may have more of a play when I get some time (In short supply at the moment :()

[Denburt]

Um... did I say that?

Actually, I was just agreeing with Rabbit that this would be difficult to set up because of the "universal" nature of the sort required. I guess technically it would work, but I still think it's a bit of an ugly solution.

Note that if the underlying query always has an absolute equivalent of each numeric field, then you would not need to change the recordsource - just set the OrderBy, as mentioned by the OP.

I agree about it's ugliness I was just looking at it project/time wise. That’s all I meant.

[Killer42]

I agree about it's ugliness I was just looking at it project/time wise. That’s all I meant.

Well, I suppose if nothing else works it's the only alternative.

[Killer42]

I have to say my curiosity was piqued so I tried it out (After 02:30 so I might have been imagining the results) and it behaved exactly as you say. IE It did nothing (Even after a .Requery).
I may have more of a play when I get some time (In short supply at the moment :()

Ahah! Check this out, from MS Access online help for the OrderByOn property...

The OrderBy property setting is applied when the object is opened.

This implies that it'll have no effect on a form that's already open. Need to develop a workaround - perhaps set them, then open a copy of the form or something?

This is also interesting...

Setting the OrderBy property for an open report will run the report's Close and Open event procedures.

Notice it doesn't say anything abut forms.

[Denburt]

I use something like the following code all the time, not for absolute values as he is requesting but it works.

Private Sub Label1_Click()
If Me.OrderBy = "Field1" Then
Me.OrderBy = "Field1 desc"
Else
Me.OrderBy = "Field1"
End If
Me.OrderByOn = True
End Sub

[Denburt]

Setting the OrderBy property for an open report will run the report's Close and Open event procedures.
Notice it doesn't say anything abut forms.

Your right I had to check it doesn't have an effect on a forms open or close event. :)

[Killer42]

Your right I had to check it doesn't have an effect on a forms open or close event. :)

Maybe so, but if you use it and it works, this is probably irrelevant.

I think the key to the whole problem may be in the online help, as well...

The OrderBy property is a string expression that is the name of the field or fields on which you want to sort records.

Perhaps you can only use actual field names, and not functions.

It's starting to sound as though it may be necessary to find another approach. Perhaps each "absolute equivalent field" could be inserted into the underlying query as needed, rather than creating them all at design time.

[Denburt]

O.K. I have tried using a function I created and a number of various things to no end. The following is ugly but if I had a lot of fields that required this then maybe this would be another approach.

I saved 2 queries named it "mysort" and "mysortDesc " in these queries I used the abs([Field1]) and it worked so you might consider this approach depending on your needs. You could even use one query just by changing it using the querydef object...

If Me.OrderBy = "Abs([Field1])" Then
DoCmd.ApplyFilter "mysortDesc"
Else
DoCmd.ApplyFilter "mysort"
End If

Query SQL

SELECT Abs([Field1]) AS Expr1
FROM Table1
ORDER BY Abs([Field1]);

[Denburt]

I used MS Access query designer and it hacked the heck out of that sql statement. I cleaned it up and it still works so all is well, I guess. Hope you find some of this usefull.