FILTER ANNULS SORT ORDER?

[Glint]

Hi Guys,
I have a form based on a query which I sorted by descending date. The query
performs fine. But I just found that when I use some filters on my form, the
sorting becomes erratic.
This is a sample of the filter:

Me.Filter = "[Zone] Is Not Null"
If Not IsNull(Area1) Then
Dim A As String
A = "SELECT Zones.ZoneID FROM Zones WHERE
(((Zones.Area)=Forms!SatsangClassSchedule!Area1))"
Me.Filter = Me.Filter & " And [Zone] IN (" & A & ")"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Area.", vbInformation,
"ECKANKAR AREA ADMIN"
If Me.RecordsetClone.RecordCount = 0 Then Exit Sub
End If

The [Zone] field is never null; so in essence, "[Zone] IS NOT NULL" should
return all records. When I run the code above, I get the reocrds I want but
do not get them sorted in descending order of date as the in underlying
query. What am I doing wrong?

 

[Allen Browne]

You can use the Z-A buttons on the ribbon/toobar to sort, but a better
workaround might be to set the RecordSource of the form rather than its
Filter.

This kind of thing:

strSql = "SELECT * FROM Table1 WHERE Zone IN (SELECT ZoneID FROM Zones WHERE
Zones.Area = """ & Forms!SatsangClassSchedule!Area1 & """) ORDER BY SomeDate
DESC;"

Me.RecordSource = strSql

 

[Glint]

Thanks, Allen.
I was really surprised that the program behaved that way: needing to
re-order a form each time the form is filtered. I guess I have to redo a
whole lot of forms that I had assumed were working fine.
Again, thanks for your help.
--
Glint

You can use the Z-A buttons on the ribbon/toobar to sort, but a better
workaround might be to set the RecordSource of the form rather than its
Filter.

This kind of thing:

strSql = "SELECT * FROM Table1 WHERE Zone IN (SELECT ZoneID FROM Zones WHERE
Zones.Area = """ & Forms!SatsangClassSchedule!Area1 & """) ORDER BY SomeDate
DESC;"

Me.RecordSource = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Guys,
I have a form based on a query which I sorted by descending date. The
query
performs fine. But I just found that when I use some filters on my form,
the
sorting becomes erratic.
This is a sample of the filter:

Me.Filter = "[Zone] Is Not Null"
If Not IsNull(Area1) Then
Dim A As String
A = "SELECT Zones.ZoneID FROM Zones WHERE
(((Zones.Area)=Forms!SatsangClassSchedule!Area1))"
Me.Filter = Me.Filter & " And [Zone] IN (" & A & ")"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Area.", vbInformation,
"ECKANKAR AREA ADMIN"
If Me.RecordsetClone.RecordCount = 0 Then Exit Sub
End If

The [Zone] field is never null; so in essence, "[Zone] IS NOT NULL" should
return all records. When I run the code above, I get the reocrds I want
but
do not get them sorted in descending order of date as the in underlying
query. What am I doing wrong?

 

[Glint]

Sorry to bother you again, Allen.
But why is it not wise to work the ORDER BY clause into the filter so that I
don't have to set the recordsource again?
--
Glint

You can use the Z-A buttons on the ribbon/toobar to sort, but a better
workaround might be to set the RecordSource of the form rather than its
Filter.

This kind of thing:

strSql = "SELECT * FROM Table1 WHERE Zone IN (SELECT ZoneID FROM Zones WHERE
Zones.Area = """ & Forms!SatsangClassSchedule!Area1 & """) ORDER BY SomeDate
DESC;"

Me.RecordSource = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Guys,
I have a form based on a query which I sorted by descending date. The
query
performs fine. But I just found that when I use some filters on my form,
the
sorting becomes erratic.
This is a sample of the filter:

Me.Filter = "[Zone] Is Not Null"
If Not IsNull(Area1) Then
Dim A As String
A = "SELECT Zones.ZoneID FROM Zones WHERE
(((Zones.Area)=Forms!SatsangClassSchedule!Area1))"
Me.Filter = Me.Filter & " And [Zone] IN (" & A & ")"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Area.", vbInformation,
"ECKANKAR AREA ADMIN"
If Me.RecordsetClone.RecordCount = 0 Then Exit Sub
End If

The [Zone] field is never null; so in essence, "[Zone] IS NOT NULL" should
return all records. When I run the code above, I get the reocrds I want
but
do not get them sorted in descending order of date as the in underlying
query. What am I doing wrong?

 

[Allen Browne]

The form's Filter and OrderBy properties are seprate. In both cases, Access
probably generates another SQL statement to execute in place of the form's
RecordSource (i.e. it seems to be smarter than fetching all the records and
then applying the filter.)

The problem is that these 2 properties can interfere with each other. As you
found creating a query that sorts the records, and then applying a filter
results in a higher level SQL statement being generated under the covers,
and so the lower level SQL statement (the one in the form's RecordSource)
does not get to sort the records in the way you specified.

There are some involved things going on here. It is even possible to get a
reduced number of records being shown in the form just because you used the
OrderBy property. In my view, that's a bug, but here's a demonstration if
you are interested:
Records disappear when you sort them
at:
http://allenbrowne.com/Bug-OrderBy.html

 

[Glint]

Thanks again, Allen.
Your explanation is very enlightening.