Sorting within a form

[Guest]

Hi,
I’m working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I’ve read several posts about this, and it’s clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s data properties
but I can’t get that to work at all, even though I’ve read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form’s data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

 

[Guest]

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.

 

[Richnep]

Hi,
I'm working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I've read several posts about this, and it's clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don't understand into
my database objects. I've tried using Order By in the form's data properties
but I can't get that to work at all, even though I've read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form's data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

Hi Ted,

I had to solve the exact problem you are trying to create and deployed
the solutuion you use

Meaning at first I had my forms record source as the table itself. The
problem was the users had the Sort Ascending/Decending buttons on the
toolbar. I looked it up in MS access and MS support said that every
time a user clicks on a field and uses the sort Ascending/Descending
command that sort order sticks. So each time a user would sort the
records, evry other user would also get it sorted in that fashon. So
to remedy this I created a simple query for the record source and used
it for default sorting. So now when users use the sort ascending/
descending buttons, it will stick for that person for that session,
but resets to the query default each time the user opens the form and
when one user sorts it doesn't affect another user. We use Access 03.

So in short, just put the sort buttons on the toolbar. The the user
clicks in the field and picks sort ascending or descending. Works for
sub tables too, Hope this helps.

 

[Guest]

Hi and thanks for helping. I tried this approach on my existing form rather
than creating a new table, etc. But it doesn't work. I set up the frame and
the option buttons and they are displayed on the form header, but I can't
activate any of the three buttons. I first let the frame default to option
1, and it showed option 1 activated, but I couldn't unactivate it and I
couldn't activate any other buttons. Any idea what's causing this?

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.

Hi,
I’m working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I’ve read several posts about this, and it’s clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s data properties
but I can’t get that to work at all, even though I’ve read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form’s data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

 

[Guest]

Hi and thanks for helping. This solution works in that it sorts the records,
but for a number of reasons I want to build the sort function into the form.
Is there a way to copy the sort icons into the form? Probably not I think...

 

[Guest]

Hi again,

I went back and tried to get your simple example to work to see if I could
learn something by doing so. Well I can't get even the simple example to
work. When I click on the radio buttons the number 1, 2 or 3 shows up in the
name field in my form, but no sorting occurs. For example, if I activate the
rank button, the number 2 appears in the first record in the name column. If
I active the serial number button, 3 appears in the first record in the name
column. Any suggestions as to what I'm doing wrong?

Thanks again for helping me with this.

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.

Hi,
I’m working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I’ve read several posts about this, and it’s clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s data properties
but I can’t get that to work at all, even though I’ve read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form’s data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

 

[Bob Quintal]

Hi,
I’m working on a continuous forms subform that has a simple
query as the record source. Originally the query sorted the
records for the form. I want to change the form so that users can
click buttons over fields and change the sort order while viewing
the records in the form. I’ve read several posts about this,
and it’s clear that others have had this problem and that there
are several ways to solve it. I am not VBA literate; I am trying
for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s
data properties but I can’t get that to work at all, even though
I’ve read the help screens and looked here as well.
Is there a way using macros on buttons and/or simple expressions
in the form’s data properties to solve this problem or do I have
to put my project on hold until I learn VBA coding?

The code is really simple, so it's a good start to lear to code.
in each button's on Click property just put the following two lines

me.orderby = "fieldname"
me.orderbyon = true

change the fieldname above to the name of the field you want that
button to sort on. If the field name has spaces or punctuation,
enclose in [] so me.orderby = "[field name]"

this will not sort descending, but it's a start.

 

[Richnep]

Hi again,

I went back and tried to get your simple example to work to see if I could
learn something by doing so. Well I can't get even the simple example to
work. When I click on the radio buttons the number 1, 2 or 3 shows up in the
name field in my form, but no sorting occurs. For example, if I activate the
rank button, the number 2 appears in the first record in the name column. If
I active the serial number button, 3 appears in the first record in the name
column. Any suggestions as to what I'm doing wrong?

Thanks again for helping me with this.

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.

"Ted M H" wrote:

- Show quoted text -

Ted,

They are not radio buttons and they are not on the form, they are
toolbar buttons. the AZ with Up or down arrows.

 

[Richnep]

Hi again,

I went back and tried to get your simple example to work to see if I could
learn something by doing so. Well I can't get even the simple example to
work. When I click on the radio buttons the number 1, 2 or 3 shows up in the
name field in my form, but no sorting occurs. For example, if I activate the
rank button, the number 2 appears in the first record in the name column. If
I active the serial number button, 3 appears in the first record in the name
column. Any suggestions as to what I'm doing wrong?

Thanks again for helping me with this.

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.

"Ted M H" wrote:

- Show quoted text -

Yeah I'm not sure how to do this on the form. The code must be out
there somewhere. Sorry.

 

[Guest]

Hi Bob, and thanks for helping me with this. It just shouldn't be so hard!
I tried your solution but couldn't get it to work. The on click property on
the property sheet for the button will only let me put things on one line, so
I can't follow your instruction (although I tried). I also tried putting in
the two lines using the Epression Builder and then the Code builder, but
neither of these worked.

The expression builder attempt put both statements in the on click property,
but then I get a msg "The expression you entered contains invalid syntax You
may have entered an operand without an operator." Expression builder also
inserts an equals sign at the front of the code.

It feels like I'm getting closer here... any addtional ideas?

Many thanks.

Hi,
I’m working on a continuous forms subform that has a simple
query as the record source. Originally the query sorted the
records for the form. I want to change the form so that users can
click buttons over fields and change the sort order while viewing
the records in the form. I’ve read several posts about this,
and it’s clear that others have had this problem and that there
are several ways to solve it. I am not VBA literate; I am trying
for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s
data properties but I can’t get that to work at all, even though
I’ve read the help screens and looked here as well.
Is there a way using macros on buttons and/or simple expressions
in the form’s data properties to solve this problem or do I have
to put my project on hold until I learn VBA coding?

The code is really simple, so it's a good start to lear to code.
in each button's on Click property just put the following two lines

me.orderby = "fieldname"
me.orderbyon = true

change the fieldname above to the name of the field you want that
button to sort on. If the field name has spaces or punctuation,
enclose in [] so me.orderby = "[field name]"

this will not sort descending, but it's a start.

 

[Guest]

Let's go back to my example (Name, Rank, SerialNumber)
Things you might have wrong...
#1 - When you created the radio buttons, did you EDIT /DUPLICATE the first
to make the 2nd and 3rd? If so, they alll have the same associated value.
Right click on each and make certain that they are 1,2,3 respectively
#2 - when you created the radio buttons, the inside of the frame that they
are in should have gone "black" when you were about to drag-and-drop them in.
Did it? If not, then the buttons aren't subservient to the frame.
#3 - Are you sure that the code is in the frame's OnClick event? To check
to see if it is firing, open the code window and click on the gray bar left
margin of the code window. Click immediately to the left of the SELECT CASE
statement. You shold get a red dot in the gray bar. This is a break point.
Save the form. Run it. When you click a radio button, the code will stop at
the break point. If it doesn't, you aren't executing the code. Press F5 to
continue.

 

[Bob Quintal]

Hi Bob, and thanks for helping me with this. It just shouldn't be
so hard! I tried your solution but couldn't get it to work. The
on click property on the property sheet for the button will only
let me put things on one line, so I can't follow your instruction
(although I tried). I also tried putting in the two lines using
the Epression Builder and then the Code builder, but neither of
these worked.

The expression builder attempt put both statements in the on click
property, but then I get a msg "The expression you entered
contains invalid syntax You may have entered an operand without an
operator." Expression builder also inserts an equals sign at the
front of the code.

It feels like I'm getting closer here... any addtional ideas?

Many thanks.

Ok, sorry I did not explain how to enter the instructions into the
VBA code editor.

When you go into any event property, there is a little dropdown menu
that you can open, with the names of all your macros plus an entry
that says [EVENT PROCEDURE]. You select that option, and then click
on the elipsis (...) button on the right side. This will open the
VBA editor, create the evend definition statement
( something like Private Sub button99_Click) and put the (End Sub)
statement below it. Just paste the two lines
me.orderby = "fieldname"
me.orderbyon = true
in between the two statements that Access created for you. Now you
can reselect the form, ( I usually size the vb editor window to half
the screen and the Access window in the other half) and repeat with
each additional button. When complete, save the code and close the
editor.

Q

Hi,
I’m working on a continuous forms subform that has a
simple query as the record source. Originally the query sorted
the records for the form. I want to change the form so that
users can click buttons over fields and change the sort order
while viewing the records in the form. I’ve read
several posts about this, and it’s clear that others
have had this problem and that there are several ways to solve
it. I am not VBA literate; I am trying for now to avoid
pasting a bunch of code I don’t understand into my
database objects. I’ve tried using Order By in the
form’s data properties but I can’t get that to
work at all, even though I’ve read the help screens and
looked here as well. Is there a way using macros on buttons
and/or simple expressions in the form’s data properties
to solve this problem or do I have to put my project on hold
until I learn VBA coding?

The code is really simple, so it's a good start to lear to code.
in each button's on Click property just put the following two
lines

me.orderby = "fieldname"
me.orderbyon = true

change the fieldname above to the name of the field you want that
button to sort on. If the field name has spaces or punctuation,
enclose in [] so me.orderby = "[field name]"

this will not sort descending, but it's a start.

 

[Guest]

Hi NK,

Thanks for hanging with me on this. Your efforts are much apprecieated.
Initially I used the control wizard to set up the frame and option buttons.
This time I turned off the wizard and created the frame. A frame is created
by using the Option Group control tool, correct? If there's a tool somewhere
called a Frame tool, I can't find it.
Access assigned the name to my frame: Frame21, and I could not find any way
to change the name to SortBy. I've changed the caption for the label to
SortBy, but that isn't going to help, is it?
I added the radio buttons one at a time and checked that their option values
are 1, 2 and 3. The frame went black when I added each radio button, so that
must mean they are subservient to the frame.
When I try to add the code as an event procedure, the VBA editor comes up
with this:

Option Compare Database

Private Sub Frame21_Click()

End Sub

I then paste in your code so it looks like this:


Option Compare Database
Option Explicit

Private Sub frame_Frame21_Click()
Select Case frame_Frame21
Case 1:
Me.OrderBy = "[Namex]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

I'm using the field name Namex instead of name because Access 2007 won't
allow me to name a field in my table Name--says it's reserved by the system.

When I click on the radio buttons nothing happens. Likewise, when I insert
the break point in the code and click the buttons, nothing happens. I
suspect the problem has something to do with the frame name and the Private
Sub statement. Can you provide additional guidance?

Many thanks.

 

[Guest]

Bob, I used your solution on NKTower's simple form and it works perfectly.
So now I know how to sort with a button and I'm pretty sure I can figure out
how to apply the same solution to a more complex form--and also how to sort
DESC and by more than one sort criteria and so on. I'm off and running.

Thanks everyone for the help. It's much appreciated.

Hi Bob, and thanks for helping me with this. It just shouldn't be
so hard! I tried your solution but couldn't get it to work. The
on click property on the property sheet for the button will only
let me put things on one line, so I can't follow your instruction
(although I tried). I also tried putting in the two lines using
the Epression Builder and then the Code builder, but neither of
these worked.

The expression builder attempt put both statements in the on click
property, but then I get a msg "The expression you entered
contains invalid syntax You may have entered an operand without an
operator." Expression builder also inserts an equals sign at the
front of the code.

It feels like I'm getting closer here... any addtional ideas?

Many thanks.

Ok, sorry I did not explain how to enter the instructions into the
VBA code editor.

When you go into any event property, there is a little dropdown menu
that you can open, with the names of all your macros plus an entry
that says [EVENT PROCEDURE]. You select that option, and then click
on the elipsis (...) button on the right side. This will open the
VBA editor, create the evend definition statement
( something like Private Sub button99_Click) and put the (End Sub)
statement below it. Just paste the two lines
me.orderby = "fieldname"
me.orderbyon = true
in between the two statements that Access created for you. Now you
can reselect the form, ( I usually size the vb editor window to half
the screen and the Access window in the other half) and repeat with
each additional button. When complete, save the code and close the
editor.

Q

in
Hi,
I’m working on a continuous forms subform that has a
simple query as the record source. Originally the query sorted
the records for the form. I want to change the form so that
users can click buttons over fields and change the sort order
while viewing the records in the form. I’ve read
several posts about this, and it’s clear that others
have had this problem and that there are several ways to solve
it. I am not VBA literate; I am trying for now to avoid
pasting a bunch of code I don’t understand into my
database objects. I’ve tried using Order By in the
form’s data properties but I can’t get that to
work at all, even though I’ve read the help screens and
looked here as well. Is there a way using macros on buttons
and/or simple expressions in the form’s data properties
to solve this problem or do I have to put my project on hold
until I learn VBA coding?

The code is really simple, so it's a good start to lear to code.
in each button's on Click property just put the following two
lines

me.orderby = "fieldname"
me.orderbyon = true

change the fieldname above to the name of the field you want that
button to sort on. If the field name has spaces or punctuation,
enclose in [] so me.orderby = "[field name]"

this will not sort descending, but it's a start.

 

[Guest]

You are VERY close. Yes, your process was correct. I suspect that Access
hasn't tumbled to the fact that there is code associated with that frame.
Here's how to check/fix it.

a) Open the form in design view.
b) Click on the line that defines the frame box (not the associated frame
label box with the text SortBy in it, but the box that encompasses the option
buttons.)
c) Click on the PROPERTIES button on the menu bar. (alternate method -
right-click on the frame box and then select PROPERTIES from the drop down.
d) Click on the EVENTS tab.
e) Scroll down - find OnClick

At this point there should be
[Event Procedure]
in that line. Probably isn't. It is a drop down, so
(f) at far right, click and select [EVENT PROCEDURE]
g) Just to the right you will see a button for this current line only with
"..." - Click it.
It will open the VBA editor positioned to the OnClick event for the frame.
It should be filled in with your version of my code.

I have seen situations where Access doesn't realize that there is code bound
to an event unless you put it there via this sequence. Don't know why. So
coming in via this sequence may very well resolve the problem.

If you do NOT have code there, it means that you have an inconsistency in
the name of the control versus the name of the event. The event proc for the
click event is controlname_Click, so you should have

Private Sub frame21_Click()

-----
Click DEBUG on main menu bar.
Click COMPILE to make sure that everything is syntactically correct.

Save, close VBA, close design view and test.

----
While we are at it - if you get to the properties page for a control and
select the ALL tab, and then scroll to the very top line - that's the name of
the control. You could then change it to 'frame_SortBy'. Note that this
really should be done before you add code as renaming it will NOT rename the
event SUBs associated with the control.
-------
Another trick: In the VBA window, at the top of the frame, are two drop
downs. The left one has GENERAL at the top of the list, and then within the
list are the various controls on the form, sections, the form itself, etc.
All things that can have code associated. If you select a control, then the
right dropdown shows all of the possible events for that control. Those with
code are in bold. Now a trick - select GGENERAL. The right drop down will
list any procs (funcions or subroutines) that are NOT associated with a
control, section, etc. If you see something liike 'fram_21' it means that it
is a proc that isn't associated with a control. That's perfectly OK as you
can write helper routines to do stuff etc. that you call from whithin events.
But in my example, it is more likely to be a typo in the name of the routine
that needs investigating.

I'll check back in tomorrow and after -it's 9:30PM Friday here.

Hi NK,

Thanks for hanging with me on this. Your efforts are much apprecieated.
Initially I used the control wizard to set up the frame and option buttons.
This time I turned off the wizard and created the frame. A frame is created
by using the Option Group control tool, correct? If there's a tool somewhere
called a Frame tool, I can't find it.
Access assigned the name to my frame: Frame21, and I could not find any way
to change the name to SortBy. I've changed the caption for the label to
SortBy, but that isn't going to help, is it?
I added the radio buttons one at a time and checked that their option values
are 1, 2 and 3. The frame went black when I added each radio button, so that
must mean they are subservient to the frame.
When I try to add the code as an event procedure, the VBA editor comes up
with this:

Option Compare Database

Private Sub Frame21_Click()

End Sub

I then paste in your code so it looks like this:


Option Compare Database
Option Explicit

Private Sub frame_Frame21_Click()
Select Case frame_Frame21
Case 1:
Me.OrderBy = "[Namex]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

I'm using the field name Namex instead of name because Access 2007 won't
allow me to name a field in my table Name--says it's reserved by the system.

When I click on the radio buttons nothing happens. Likewise, when I insert
the break point in the code and click the buttons, nothing happens. I
suspect the problem has something to do with the frame name and the Private
Sub statement. Can you provide additional guidance?

Many thanks.

Let's go back to my example (Name, Rank, SerialNumber)
Things you might have wrong...
#1 - When you created the radio buttons, did you EDIT /DUPLICATE the first
to make the 2nd and 3rd? If so, they alll have the same associated value.
Right click on each and make certain that they are 1,2,3 respectively
#2 - when you created the radio buttons, the inside of the frame that they
are in should have gone "black" when you were about to drag-and-drop them in.
Did it? If not, then the buttons aren't subservient to the frame.
#3 - Are you sure that the code is in the frame's OnClick event? To check
to see if it is firing, open the code window and click on the gray bar left
margin of the code window. Click immediately to the left of the SELECT CASE
statement. You shold get a red dot in the gray bar. This is a break point.
Save the form. Run it. When you click a radio button, the code will stop at
the break point. If it doesn't, you aren't executing the code. Press F5 to
continue.

 

[Guest]

Hi NKTower, Bob and Richnep,

I was finally able to implement Bob's solution to my problem. There was one
thing that really threw me for a loop though, and I thought I'd pass it on to
you just in case you're interested.

If the field name that you want to have your command button sort by has a #
at its end the solution won't work. As it turns out, all of the fields I
wanted to sort by ended in # (Part#, SO#, ShipTO#, etc.). I could get the
solution to work perfectly with test data (which coincidently had no fields
ending with #), but no matter what I tried it wouldn't work on my real
problem. I finally discovered this # nuance by accident while stumbling
around trying to get it to work.

All I did was go into the underlying query and get rid of the bad field
names (I used expressions such as PARTNO =[Part#] because I can't change the
field names in my client's table). Now it's working fine.

Again, thanks for you help.