Sort form by absolute value

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Killer42
    Recognized Expert Expert
    • Oct 2006
    • 8429

    #31
    Originally posted by 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.
    Yes, (I thought it seemed ((a bit short on (((parentheses) ))))).

    Comment

    • Denburt
      Recognized Expert Top Contributor
      • Mar 2007
      • 1356

      #32
      Originally posted by Killer42
      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.

      Code:
      SELECT *
      FROM [SELECT Table1.Field1, Table1.Dated FROM Table1;]. AS Form1
      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.

      Comment

      • Killer42
        Recognized Expert Expert
        • Oct 2006
        • 8429

        #33
        Originally posted by Denburt
        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.

        Code:
        SELECT *
        FROM [SELECT Table1.Field1, Table1.Dated FROM Table1;][U].[/U] AS Form1
        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".

        Comment

        • MMcCarthy
          Recognized Expert MVP
          • Aug 2006
          • 14387

          #34
          Originally posted by Killer42
          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.

          Comment

          • Denburt
            Recognized Expert Top Contributor
            • Mar 2007
            • 1356

            #35
            Originally posted by Killer42
            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.

            Comment

            • Killer42
              Recognized Expert Expert
              • Oct 2006
              • 8429

              #36
              Originally posted by mmccarthy
              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,

              Comment

              • dima69
                Recognized Expert New Member
                • Sep 2006
                • 181

                #37
                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 .

                Comment

                • MMcCarthy
                  Recognized Expert MVP
                  • Aug 2006
                  • 14387

                  #38
                  Originally posted by Killer42
                  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

                  Comment

                  • NeoPa
                    Recognized Expert Moderator MVP
                    • Oct 2006
                    • 32669

                    #39
                    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.

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32669

                      #40
                      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.
                      Code:
                      'GetSQL gets the SQL component from a named query OR a SQL string.
                      'When subqueries are specified in MS Access they are changed internally
                      'from   "FROM (SELECT blah blah blah) AS" to
                      'either "FROM [SELECT blah blah blah]. AS"
                      'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
                      'This code assumes any effected subquery will start with " [SELECT ".
                      'This reverts SQL to correct format and loses ';' at end if requested.
                      ' 11/11/2004    Updated to detect second problem and to use InStr
                      Public Function GetSQL(strQuery As String, _
                                             Optional blnLoseSC As Boolean = True) As String
                          Dim intDepth As Integer
                          Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long
                      
                          'If param passed is already SQL then leave as is - Otherwise get from query
                          If Left(strQuery, 1) = "(" Then
                              GetSQL = strQuery
                          Else
                              On Error Resume Next
                              GetSQL = CurrentDb.QueryDefs(strQuery).SQL
                          End If
                          lngLeft = -7&
                          Do
                              'Check for corrupted subquery
                              lngLeft = InStr(lngLeft + 8&, GetSQL, " [SELECT ", vbTextCompare)
                              If lngLeft = 0& Then Exit Do
                              'To find end correctly we must treat '[' & ']' as matched pairs
                              intDepth = 1
                              lngRight = lngLeft + 8&
                              lngOpen = -lngRight
                              Do
                                  'Find next ']'
                                  lngRight = InStr(lngRight + 1&, GetSQL, "]", vbBinaryCompare)
                                  If lngRight = 0& Then
                                      GetSQL = ""
                                      Exit Function
                                  End If
                                  intDepth = intDepth - 1
                                  Do
                                      'For lngOpen negative numbers mean that item has been counted
                                      'If already counted get next one - Otherwise drop through
                                      If lngOpen < 0& Then _
                                          lngOpen = InStr(-lngOpen + 1&, GetSQL, "[", vbBinaryCompare)
                                      'we're only interested (now) if it found one BEFORE the ']'
                                      If lngOpen > lngRight Or lngOpen = 0& Then Exit Do
                                      intDepth = intDepth + 1
                                      lngOpen = -lngOpen
                                  Loop
                              Loop While intDepth > 0
                              'If '].' found then be sure to drop the '.' too
                              lngAdjust = IIf(Mid(GetSQL, lngRight + 1&, 1) = ".", 1&, 0&)
                              GetSQL = Left(GetSQL, lngLeft) & "(" & _
                                       Mid(GetSQL, lngLeft + 2&, lngRight - lngLeft - 2&) & ")" & _
                                       Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
                          Loop
                          'Lose ";" at end if requested and it exists
                          If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
                              GetSQL = Left(GetSQL, Len(GetSQL) - 3)
                      End Function

                      Comment

                      • Denburt
                        Recognized Expert Top Contributor
                        • Mar 2007
                        • 1356

                        #41
                        Originally posted by Killer42
                        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.

                        Comment

                        • dima69
                          Recognized Expert New Member
                          • Sep 2006
                          • 181

                          #42
                          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:
                          Code:
                          Dim qd As QueryDef, cdb As Database
                          Set cdb = CurrentDb
                          Set qd = cdb.QueryDefs("MyFilter")
                          qd.SQL = "SELECT * FROM NoSuchTable" & _
                          IIf(Forms("frm1").FilterOn = True, " WHERE " & Forms("frm1").Filter, "") & _
                          " ORDER BY ABS([theSum])"
                          Set qd = Nothing
                          Set cdb = Nothing
                          Forms("frm1").SetFocus
                          DoCmd.ApplyFilter "MyFilter"
                          and it seems to work. (WHERE is added optionally to keep existing filter)

                          Comment

                          • Denburt
                            Recognized Expert Top Contributor
                            • Mar 2007
                            • 1356

                            #43
                            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!

                            Comment

                            • dima69
                              Recognized Expert New Member
                              • Sep 2006
                              • 181

                              #44
                              One unexpected problem ...
                              DoCmd.ApplyFilt er 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 ?

                              Comment

                              • dima69
                                Recognized Expert New Member
                                • Sep 2006
                                • 181

                                #45
                                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:
                                Code:
                                Sub ApplyABS(frm As Form, fldName As String)
                                Dim strRS As String, FOn As Boolean
                                strRS = Trim(frm.RecordSource)
                                FOn = Me.FilterOn
                                If Right(strRS, 1) = ";" Then strRS = Left(strRS, Len(strRS) - 1)
                                frm.RecordSource = "SELECT *, ABS([" & fldName & "]) AS ABS_1 FROM (" & strRS & ")"
                                If FOn = True Then Me.FilterOn = True
                                frm.OrderBy = "ABS_1"
                                frm.OrderByOn = True
                                End Sub

                                Comment

                                Working...