Originally posted by Denburt
Sort form by absolute value
Collapse
X
-
LOL, not parenthesis. I can't tell you the last time I used the toolbar to create a filter but man.Originally posted by Killer42Yes, (I thought it seemed ((a bit short on (((parentheses) ))))).
Posting the code so you can see the hacked up Access version.
I am sure it probably does this so Access can call the same query when requested but it's still looks nasty.Code:SELECT * FROM [SELECT Table1.Field1, Table1.Dated FROM Table1;]. AS Form1 ORDER BY Abs([Field1]);
Comment
-
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.Originally posted by DenburtLOL, 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.
I am sure it probably does this so Access can call the same query when requested but it's still looks nasty.Code:SELECT * FROM [SELECT Table1.Field1, Table1.Dated FROM Table1;][U].[/U] AS Form1 ORDER BY Abs([Field1]);
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
-
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.Originally posted by Killer42Is 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
-
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.Originally posted by Killer42Is 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
-
Yikes! That's pretty scarey. I often use the designer to build queries, then suck them out of the SQL view,Originally posted by mmccarthyIf 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
-
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
-
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 FunctionComment
-
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.Originally posted by Killer42Yikes! That's pretty scarey. I often use the designer to build queries, then suck them out of the SQL view,Comment
-
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:
and it seems to work. (WHERE is added optionally to keep existing filter)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"Comment
-
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
-
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 SubComment
Comment