469,327 Members | 1,292 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

Sort form by absolute value

dima69
181 Expert 100+
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\sorting" from "Records" menu, it works just fine, and OrderBy proprty becomes "Abs([theSum])". But when I do the same thing manually or programmatically, it doesn't work.
So I cannot figure out what is the trick here and how to make this work.
Apr 1 '07 #1
49 5610
Rabbit
12,516 Expert Mod 8TB
What's the code you're using?
Apr 1 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
If you set the order by in the query behind the form it should work.
Apr 2 '07 #3
dima69
181 Expert 100+
What's the code you're using?
The code is very simple:
Expand|Select|Wrap|Line Numbers
  1. Me.OrderBy = "Abs([theSum])"
  2. Me.OrderByOn = TRUE
Apr 2 '07 #4
dima69
181 Expert 100+
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" ?
Apr 2 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Just to ask the obvious. Are you sure the textbox name corresponding to this field is "theSum"
Apr 2 '07 #6
dima69
181 Expert 100+
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 ?
Apr 2 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
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. :)
Apr 2 '07 #8
MMcCarthy
14,534 Expert Mod 8TB
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
Apr 2 '07 #9
Rabbit
12,516 Expert Mod 8TB
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.
Apr 2 '07 #10
Denburt
1,356 Expert 1GB
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.
Apr 2 '07 #11
dima69
181 Expert 100+
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.
Apr 2 '07 #12
dima69
181 Expert 100+
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.
Apr 2 '07 #13
Rabbit
12,516 Expert Mod 8TB
Well then what about building a SQL statement in code that will become the form's recordsource?
Apr 2 '07 #14
nico5038
3,080 Expert 2GB
Then why not add the ABS() value to the form's recordsource and use that in the "normal" way.

Nic;o)
Apr 2 '07 #15
Rabbit
12,516 Expert Mod 8TB
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.
Apr 2 '07 #16
Killer42
8,435 Expert 8TB
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.
Apr 2 '07 #17
Denburt
1,356 Expert 1GB
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...
Apr 2 '07 #18
Killer42
8,435 Expert 8TB
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.
Apr 2 '07 #19
NeoPa
32,181 Expert Mod 16PB
The code is very simple:
Expand|Select|Wrap|Line Numbers
  1. Me.OrderBy = "Abs([theSum])"
  2. 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?
Apr 3 '07 #20
dima69
181 Expert 100+
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.
Apr 3 '07 #21
NeoPa
32,181 Expert Mod 16PB
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 :()
Apr 3 '07 #22
Denburt
1,356 Expert 1GB
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.
Apr 3 '07 #23
Killer42
8,435 Expert 8TB
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.
Apr 3 '07 #24
Killer42
8,435 Expert 8TB
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.
Apr 3 '07 #25
Denburt
1,356 Expert 1GB
I use something like the following code all the time, not for absolute values as he is requesting but it works.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Label1_Click()
  2. If Me.OrderBy = "Field1" Then
  3. Me.OrderBy = "Field1 desc"
  4. Else
  5. Me.OrderBy = "Field1"
  6. End If
  7. Me.OrderByOn = True
  8. End Sub
  9.  
Apr 3 '07 #26
Denburt
1,356 Expert 1GB
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. :)
Apr 3 '07 #27
Killer42
8,435 Expert 8TB
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.
Apr 3 '07 #28
Denburt
1,356 Expert 1GB
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...

Expand|Select|Wrap|Line Numbers
  1. If Me.OrderBy = "Abs([Field1])" Then
  2. DoCmd.ApplyFilter "mysortDesc"
  3. Else
  4. DoCmd.ApplyFilter "mysort"
  5. End If
  6.  
Query SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT Abs([Field1]) AS Expr1
  2. FROM Table1
  3. ORDER BY Abs([Field1]);
  4.  
Apr 3 '07 #29
Denburt
1,356 Expert 1GB
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.
Apr 3 '07 #30
Killer42
8,435 Expert 8TB
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.
Yes, (I thought it seemed ((a bit short on (((parentheses)))))).
Apr 4 '07 #31
Denburt
1,356 Expert 1GB
Yes, (I thought it seemed ((a bit short on (((parentheses)))))).
LOL, not parenthesis. I can't tell you the last time I used the toolbar to create a filter but man.

Posting the code so you can see the hacked up Access version.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [SELECT Table1.Field1, Table1.Dated FROM Table1;]. AS Form1
  3. ORDER BY Abs([Field1]);
I am sure it probably does this so Access can call the same query when requested but it's still looks nasty.
Apr 4 '07 #32
Killer42
8,435 Expert 8TB
LOL, not parenthesis. I can't tell you the last time I used the toolbar to create a filter but man.

Posting the code so you can see the hacked up Access version.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [SELECT Table1.Field1, Table1.Dated FROM Table1;]. AS Form1
  3. ORDER BY Abs([Field1]);
I am sure it probably does this so Access can call the same query when requested but it's still looks nasty.
Is that a straight copy and paste? If so, I'm rather curious about the dot, which I have underlined. Haven't seen that syntax before.

Also, it certainly does seem weird that Access created two queries here. Doesn't that affect performance? Oops! I keep forgetting, Access doesn't do "the P word".
Apr 4 '07 #33
MMcCarthy
14,534 Expert Mod 8TB
Is that a straight copy and paste? If so, I'm rather curious about the dot, which I have underlined. Haven't seen that syntax before.

Also, it certainly does seem weird that Access created two queries here. Doesn't that affect performance? Oops! I keep forgetting, Access doesn't do "the P word".
If you use a subquery in Access it sometimes interprets it this way. It then no longer works and has to be rewritten. The trick with subqueries is not to edit them in the designer as this is what seems to prompt this interpretation by the Jet Engine.
Apr 4 '07 #34
Denburt
1,356 Expert 1GB
Is that a straight copy and paste? If so, I'm rather curious about the dot, which I have underlined. Haven't seen that syntax before.

Also, it certainly does seem weird that Access created two queries here. Doesn't that affect performance? Oops! I keep forgetting, Access doesn't do "the P word".
Yes that was a straight copy paste, it is pretty screwy thats why I posted it. Like Mary stated when you view a MS Access query that has a subquery in SQL view you end up with this mess, and the query no longer functions. You then need to change the brackets to parenthesis and the dot should be removed and it will work again. Yeah and the whole performance thing... LOL That is the whole reason I posted the original query since that is all you really need to use the applyfilter command. Oh and using the designer is usually O.K. just when you look at it in SQL view thats the only time I have witnessed this.
Apr 4 '07 #35
Killer42
8,435 Expert 8TB
If you use a subquery in Access it sometimes interprets it this way. It then no longer works and has to be rewritten. The trick with subqueries is not to edit them in the designer as this is what seems to prompt this interpretation by the Jet Engine.
Yikes! That's pretty scarey. I often use the designer to build queries, then suck them out of the SQL view,
Apr 5 '07 #36
dima69
181 Expert 100+
Thanks for everybody for the replies on this thread. I've been out of town for a couple of days, disconnected from the net. :)
I'll keep up and examine closely all of the suggestions you made here .
Apr 5 '07 #37
MMcCarthy
14,534 Expert Mod 8TB
Yikes! That's pretty scarey. I often use the designer to build queries, then suck them out of the SQL view,
It's only a problem with subqueries. Just be careful when using them.

Mary
Apr 5 '07 #38
NeoPa
32,181 Expert Mod 16PB
I too was away as my phone line died on me yesterday completely (@home).
Anyway, the replacing of the () with []. around a sub-query has been in Access for a while now, unfortunately. At least in 2K3 and beyond, it actually knows how to interpret its own b4lls-ups and can switch you back to design-view without complaining about its own errors. This won't help you to port it elsewhere mind you.
Apr 5 '07 #39
NeoPa
32,181 Expert Mod 16PB
In case anyone's interested I have a routine I use to get SQL strings without the Access added 'improvements'.
I can't guarantee it will work entirely as is for everybody as I may use other, non-standard, functions in it somewhere, and I may have assumed that the subqueries will only appear in the FROM clause (A misappreciation I held until recently). It should provide a decent base to work from if anyone's interested though.
Expand|Select|Wrap|Line Numbers
  1. 'GetSQL gets the SQL component from a named query OR a SQL string.
  2. 'When subqueries are specified in MS Access they are changed internally
  3. 'from   "FROM (SELECT blah blah blah) AS" to
  4. 'either "FROM [SELECT blah blah blah]. AS"
  5. 'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
  6. 'This code assumes any effected subquery will start with " [SELECT ".
  7. 'This reverts SQL to correct format and loses ';' at end if requested.
  8. ' 11/11/2004    Updated to detect second problem and to use InStr
  9. Public Function GetSQL(strQuery As String, _
  10.                        Optional blnLoseSC As Boolean = True) As String
  11.     Dim intDepth As Integer
  12.     Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long
  13.  
  14.     'If param passed is already SQL then leave as is - Otherwise get from query
  15.     If Left(strQuery, 1) = "(" Then
  16.         GetSQL = strQuery
  17.     Else
  18.         On Error Resume Next
  19.         GetSQL = CurrentDb.QueryDefs(strQuery).SQL
  20.     End If
  21.     lngLeft = -7&
  22.     Do
  23.         'Check for corrupted subquery
  24.         lngLeft = InStr(lngLeft + 8&, GetSQL, " [SELECT ", vbTextCompare)
  25.         If lngLeft = 0& Then Exit Do
  26.         'To find end correctly we must treat '[' & ']' as matched pairs
  27.         intDepth = 1
  28.         lngRight = lngLeft + 8&
  29.         lngOpen = -lngRight
  30.         Do
  31.             'Find next ']'
  32.             lngRight = InStr(lngRight + 1&, GetSQL, "]", vbBinaryCompare)
  33.             If lngRight = 0& Then
  34.                 GetSQL = ""
  35.                 Exit Function
  36.             End If
  37.             intDepth = intDepth - 1
  38.             Do
  39.                 'For lngOpen negative numbers mean that item has been counted
  40.                 'If already counted get next one - Otherwise drop through
  41.                 If lngOpen < 0& Then _
  42.                     lngOpen = InStr(-lngOpen + 1&, GetSQL, "[", vbBinaryCompare)
  43.                 'we're only interested (now) if it found one BEFORE the ']'
  44.                 If lngOpen > lngRight Or lngOpen = 0& Then Exit Do
  45.                 intDepth = intDepth + 1
  46.                 lngOpen = -lngOpen
  47.             Loop
  48.         Loop While intDepth > 0
  49.         'If '].' found then be sure to drop the '.' too
  50.         lngAdjust = IIf(Mid(GetSQL, lngRight + 1&, 1) = ".", 1&, 0&)
  51.         GetSQL = Left(GetSQL, lngLeft) & "(" & _
  52.                  Mid(GetSQL, lngLeft + 2&, lngRight - lngLeft - 2&) & ")" & _
  53.                  Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
  54.     Loop
  55.     'Lose ";" at end if requested and it exists
  56.     If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
  57.         GetSQL = Left(GetSQL, Len(GetSQL) - 3)
  58. End Function
Apr 5 '07 #40
Denburt
1,356 Expert 1GB
Yikes! That's pretty scarey. I often use the designer to build queries, then suck them out of the SQL view,
I do the same thing and when I am using sub queries, I guess I will be taking a closer look a Neo's suggestion that could save me some time with clean up. Thanks Neo.
Apr 5 '07 #41
dima69
181 Expert 100+
Wow ! This stuff works ! Somehow, I never used ApplyFilter function with a query name as an argument - thought it was redundant.
Actually, now I see that the filter query does not have to contain any meaningful data at all, except for the "WHERE ... " and "ORDER BY ... " expressions.
I wrote this sample code:
Expand|Select|Wrap|Line Numbers
  1. Dim qd As QueryDef, cdb As Database
  2. Set cdb = CurrentDb
  3. Set qd = cdb.QueryDefs("MyFilter")
  4. qd.SQL = "SELECT * FROM NoSuchTable" & _
  5. IIf(Forms("frm1").FilterOn = True, " WHERE " & Forms("frm1").Filter, "") & _
  6. " ORDER BY ABS([theSum])"
  7. Set qd = Nothing
  8. Set cdb = Nothing
  9. Forms("frm1").SetFocus
  10. DoCmd.ApplyFilter "MyFilter"
  11.  
and it seems to work. (WHERE is added optionally to keep existing filter)
Apr 5 '07 #42
Denburt
1,356 Expert 1GB
Nicely done... Glad we could help.

Oh, and a note I like squeezing MS Access for every little bit of speed I can so I thought I would toss this out there.


Control Object Reference (Me!)


As far as I am aware this is also true for forms.

Forms("frm1")

Would be a tab bit slower then

Forms!frm1

Good luck and happy Coding!
Apr 5 '07 #43
dima69
181 Expert 100+
One unexpected problem ...
DoCmd.ApplyFilter only works on the main form, not on the SubForm. SetFocus does not help this time, as in case with other DoCmd methods.
Any suggestions ?
Apr 8 '07 #44
dima69
181 Expert 100+
Well, seems that all this is worthless because of that stupid access bug. So I think that after all I will change the record source dynamically to include the ABS value, something like:
Expand|Select|Wrap|Line Numbers
  1. Sub ApplyABS(frm As Form, fldName As String)
  2. Dim strRS As String, FOn As Boolean
  3. strRS = Trim(frm.RecordSource)
  4. FOn = Me.FilterOn
  5. If Right(strRS, 1) = ";" Then strRS = Left(strRS, Len(strRS) - 1)
  6. frm.RecordSource = "SELECT *, ABS([" & fldName & "]) AS ABS_1 FROM (" & strRS & ")"
  7. If FOn = True Then Me.FilterOn = True
  8. frm.OrderBy = "ABS_1"
  9. frm.OrderByOn = True
  10. End Sub
Apr 9 '07 #45
Killer42
8,435 Expert 8TB
I guess you can't win 'em all. But, if it works that's the important thing.
Apr 9 '07 #46
Denburt
1,356 Expert 1GB
Interesting how you can run the advaced filter/sort from the menu, for the subfom, and that seemed to work fine but it will not work in code....

This is something I did not know, glad it's working for you.
Apr 9 '07 #47
Killer42
8,435 Expert 8TB
Interesting how you can run the advaced filter/sort from the menu, for the subfom, and that seemed to work fine but it will not work in code....

This is something I did not know, glad it's working for you.
Is it definitely a bug, or just something we haven't worked out yet?
Apr 9 '07 #48
Denburt
1,356 Expert 1GB
Is it definitely a bug, or just something we haven't worked out yet?
Microsoft specified that it is not possible to use applyfilter to a subform in code and we should change the recordsource. :(

Although this article only states it is referencing db's up to MS Access 97 I think the same still applies.

http://support.microsoft.com/kb/112796
Apr 9 '07 #49
NeoPa
32,181 Expert Mod 16PB
Well, seems that all this is worthless because of that stupid access bug. So I think that after all I will change the record source dynamically to include the ABS value, something like:
Expand|Select|Wrap|Line Numbers
  1. Sub ApplyABS(frm As Form, fldName As String)
  2. Dim strRS As String, FOn As Boolean
  3. strRS = Trim(frm.RecordSource)
  4. FOn = Me.FilterOn
  5. If Right(strRS, 1) = ";" Then strRS = Left(strRS, Len(strRS) - 1)
  6. frm.RecordSource = "SELECT *, ABS([" & fldName & "]) AS ABS_1 FROM (" & strRS & ")"
  7. If FOn = True Then Me.FilterOn = True
  8. frm.OrderBy = "ABS_1"
  9. frm.OrderByOn = True
  10. End Sub
You should bear in mind that the ";" at the end is normally stored as ";<CR><LF>" (3 char string) so your stripping code would fail in its current incarnation.
What's also worth bearing in mind is that Access sometimes corrupts its stored SQL when handling subqueries.
Apr 10 '07 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Paul THompson | last post: by
3 posts views Thread by Neil | last post: by
2 posts views Thread by Robert Smith | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.