472,146 Members | 1,376 Online

# Set sort order in MS Access 2000 report

Hello,

I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How could I sort it as:
Low, Medium, High?

Any suggestion is greatly appreciated,
James
Nov 13 '05 #1
11 3744
James, Don't know how you can, but what you could do is base the report on a
below statement to the top row (Field:) of an empty column in you query grid
txtSort:
IIf([Priority]="Low",1,IIf([Priority]="Medium",2,IIf([Priority]="High",3)))

Now you can sort by this field either in the query or via the sorting and
grouping in the report.

Hope this helps!

Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
Hello,

I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How could I sort it as:
Low, Medium, High?

Any suggestion is greatly appreciated,
James

Nov 13 '05 #2
"James P." <ha*********@yahoo.com> wrote in message
Hello,

I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How could I sort it as:
Low, Medium, High?

Any suggestion is greatly appreciated,
James

You are sorting by a text field. Best to use a numeric code to represent
the levels with a lookup table to get the text, makes sorting much
easier.

eg.
1 High
2 Medium
3 Low
--
regards,

Nov 13 '05 #3
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message news:<i6********************@comcast.com>...
James, Don't know how you can, but what you could do is base the report on a
below statement to the top row (Field:) of an empty column in you query grid
txtSort:
IIf([Priority]="Low",1,IIf([Priority]="Medium",2,IIf([Priority]="High",3)))

Now you can sort by this field either in the query or via the sorting and
grouping in the report.

Hope this helps!

Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
Hello,

I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How could I sort it as:
Low, Medium, High?

Any suggestion is greatly appreciated,
James

Thank you, Reggie.
Nov 13 '05 #4
"Bradley.-." <sp********@mail.com> wrote in message news:<A3*******************@news-server.bigpond.net.au>...
"James P." <ha*********@yahoo.com> wrote in message
Hello,

I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How could I sort it as:
Low, Medium, High?

Any suggestion is greatly appreciated,
James

You are sorting by a text field. Best to use a numeric code to represent
the levels with a lookup table to get the text, makes sorting much
easier.

eg.
1 High
2 Medium
3 Low

I followed Reggie's suggestion (and also from Bradley, thank you).
One thing I did not mentioned before was I have another Name sort and
grouping over Priority. Which means Name is the main sort and
grouping, then Priority (I named it PriorityNum). For debugging
purpose, I put PriorityNum (numeric) in both detail line and also in

Whenever I ran the report, it prompted as: "Enter parameter value:" in
the box title, and it asked for PriorityNum. I entered nothing. The
result I got was the Priority numeric printed correctly at the detail
line, but the grouping header (I put as = [PriorityNum]), it did not
break correctly. For example, it shows PriorityNum as 2 at the header
grouping, but in that detail group, it shows all 2, 1 and 3 mixed up.

Any other suggestions? Thank you in advance.
James
Nov 13 '05 #5
James, I believe that is because you are using a reserved word for your
field "Name". Change this to txtName or something else and see if this
doesn't fix your problem. If not post back!

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message

news:<A3*******************@news-server.bigpond.net.au>...
"James P." <ha*********@yahoo.com> wrote in message
Hello,

I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How could I sort it as:
Low, Medium, High?

Any suggestion is greatly appreciated,
James

You are sorting by a text field. Best to use a numeric code to represent
the levels with a lookup table to get the text, makes sorting much
easier.

eg.
1 High
2 Medium
3 Low

I followed Reggie's suggestion (and also from Bradley, thank you).
One thing I did not mentioned before was I have another Name sort and
grouping over Priority. Which means Name is the main sort and
grouping, then Priority (I named it PriorityNum). For debugging
purpose, I put PriorityNum (numeric) in both detail line and also in

Whenever I ran the report, it prompted as: "Enter parameter value:" in
the box title, and it asked for PriorityNum. I entered nothing. The
result I got was the Priority numeric printed correctly at the detail
line, but the grouping header (I put as = [PriorityNum]), it did not
break correctly. For example, it shows PriorityNum as 2 at the header
grouping, but in that detail group, it shows all 2, 1 and 3 mixed up.

Any other suggestions? Thank you in advance.
James

Nov 13 '05 #6
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message news:<Rc********************@comcast.com>...
James, I believe that is because you are using a reserved word for your
field "Name". Change this to txtName or something else and see if this
doesn't fix your problem. If not post back!

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message

news:<A3*******************@news-server.bigpond.net.au>...
"James P." <ha*********@yahoo.com> wrote in message
> Hello,
>
> I have a report with the Priority field is used as sort order and
> grouping. The problem is the data in this Priority field if sorted in
> ascending order is: High, Low, and Medium. How could I sort it as:
> Low, Medium, High?
>
> Any suggestion is greatly appreciated,
> James

You are sorting by a text field. Best to use a numeric code to represent
the levels with a lookup table to get the text, makes sorting much
easier.

eg.
1 High
2 Medium
3 Low

I followed Reggie's suggestion (and also from Bradley, thank you).
One thing I did not mentioned before was I have another Name sort and
grouping over Priority. Which means Name is the main sort and
grouping, then Priority (I named it PriorityNum). For debugging
purpose, I put PriorityNum (numeric) in both detail line and also in

Whenever I ran the report, it prompted as: "Enter parameter value:" in
the box title, and it asked for PriorityNum. I entered nothing. The
result I got was the Priority numeric printed correctly at the detail
line, but the grouping header (I put as = [PriorityNum]), it did not
break correctly. For example, it shows PriorityNum as 2 at the header
grouping, but in that detail group, it shows all 2, 1 and 3 mixed up.

Any other suggestions? Thank you in advance.
James

Reggie,

In my program, I used txtAssignee instead of Name. Sorry, for the
confusion in my email when I tried to make it simple by using Name
instead of the real field name. So, here is my layout is supposed to
look like:

1) First sort and grouping: txtAssignee : in header group 1.
2) Second sort and grouping: txtPriority = [PriorityNum]. This part
is in the header group 2.
PriorityNum = Your IIF statement that you gave me.

James
Nov 13 '05 #7
James, Open your sorting & grouping box. Now select the txtAssignee first
from the drop down list then the txtPriority or PriorityNum second. From
your post a I can't tell what the control is because of this statement
"txtPriority = [PriorityNum]". It sounds like you are trying to group by
[PriorityNum] in you sorting & grouping box, but that field doesn't exist in
your recordsource anymore because you renamed it to txtPriority in the
query. Hope this helps, if not post back!

1) First sort and grouping: txtAssignee : in header group 1.
2) Second sort and grouping: txtPriority = [PriorityNum]. This part
is in the header group 2.
PriorityNum = Your IIF statement that you gave me.

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
news:f4**************************@posting.google.c om... "Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message

news:<Rc********************@comcast.com>...
James, I believe that is because you are using a reserved word for your
field "Name". Change this to txtName or something else and see if this
doesn't fix your problem. If not post back!

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message

news:<A3*******************@news-server.bigpond.net.au>...
> "James P." <ha*********@yahoo.com> wrote in message
> > Hello,
> >
> > I have a report with the Priority field is used as sort order and
> > grouping. The problem is the data in this Priority field if sorted in > > ascending order is: High, Low, and Medium. How could I sort it as: > > Low, Medium, High?
> >
> > Any suggestion is greatly appreciated,
> > James
>
> You are sorting by a text field. Best to use a numeric code to represent > the levels with a lookup table to get the text, makes sorting much
> easier.
>
> eg.
> 1 High
> 2 Medium
> 3 Low

I followed Reggie's suggestion (and also from Bradley, thank you).
One thing I did not mentioned before was I have another Name sort and
grouping over Priority. Which means Name is the main sort and
grouping, then Priority (I named it PriorityNum). For debugging
purpose, I put PriorityNum (numeric) in both detail line and also in

Whenever I ran the report, it prompted as: "Enter parameter value:" in
the box title, and it asked for PriorityNum. I entered nothing. The
result I got was the Priority numeric printed correctly at the detail
line, but the grouping header (I put as = [PriorityNum]), it did not
break correctly. For example, it shows PriorityNum as 2 at the header
grouping, but in that detail group, it shows all 2, 1 and 3 mixed up.

Any other suggestions? Thank you in advance.
James

Reggie,

In my program, I used txtAssignee instead of Name. Sorry, for the
confusion in my email when I tried to make it simple by using Name
instead of the real field name. So, here is my layout is supposed to
look like:

1) First sort and grouping: txtAssignee : in header group 1.
2) Second sort and grouping: txtPriority = [PriorityNum]. This part
is in the header group 2.
PriorityNum = Your IIF statement that you gave me.

James

Nov 13 '05 #8
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message news:<OL********************@comcast.com>...
James, Open your sorting & grouping box. Now select the txtAssignee first
from the drop down list then the txtPriority or PriorityNum second. From
your post a I can't tell what the control is because of this statement
"txtPriority = [PriorityNum]". It sounds like you are trying to group by
[PriorityNum] in you sorting & grouping box, but that field doesn't exist in
your recordsource anymore because you renamed it to txtPriority in the
query. Hope this helps, if not post back!

1) First sort and grouping: txtAssignee : in header group 1.
2) Second sort and grouping: txtPriority = [PriorityNum]. This part
is in the header group 2.
PriorityNum = Your IIF statement that you gave me.

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message

news:<Rc********************@comcast.com>...
James, I believe that is because you are using a reserved word for your
field "Name". Change this to txtName or something else and see if this
doesn't fix your problem. If not post back!

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
> "Bradley.-." <sp********@mail.com> wrote in message news:<A3*******************@news-server.bigpond.net.au>... > > "James P." <ha*********@yahoo.com> wrote in message
> > > Hello,
> > >
> > > I have a report with the Priority field is used as sort order and
> > > grouping. The problem is the data in this Priority field if sorted in > > > ascending order is: High, Low, and Medium. How could I sort it as: > > > Low, Medium, High?
> > >
> > > Any suggestion is greatly appreciated,
> > > James
> >
> > You are sorting by a text field. Best to use a numeric code to represent > > the levels with a lookup table to get the text, makes sorting much
> > easier.
> >
> > eg.
> > 1 High
> > 2 Medium
> > 3 Low
>
> I followed Reggie's suggestion (and also from Bradley, thank you).
> One thing I did not mentioned before was I have another Name sort and
> grouping over Priority. Which means Name is the main sort and
> grouping, then Priority (I named it PriorityNum). For debugging
> purpose, I put PriorityNum (numeric) in both detail line and also in
>
> Whenever I ran the report, it prompted as: "Enter parameter value:" in
> the box title, and it asked for PriorityNum. I entered nothing. The
> result I got was the Priority numeric printed correctly at the detail
> line, but the grouping header (I put as = [PriorityNum]), it did not
> break correctly. For example, it shows PriorityNum as 2 at the header
> grouping, but in that detail group, it shows all 2, 1 and 3 mixed up.
>
> Any other suggestions? Thank you in advance.
> James

Reggie,

In my program, I used txtAssignee instead of Name. Sorry, for the
confusion in my email when I tried to make it simple by using Name
instead of the real field name. So, here is my layout is supposed to
look like:

1) First sort and grouping: txtAssignee : in header group 1.
2) Second sort and grouping: txtPriority = [PriorityNum]. This part
is in the header group 2.
PriorityNum = Your IIF statement that you gave me.

James

Reggie,

I used txtPriority as group header 2. It refers to PriorityNum in the
detail line. PriorityNum is defined with your IIF statement. Neither
PriorityNum nor TxtPriority shown in the dropdown list of Sorting &
Grouping. I guess because these two fields I defined it myself and
they are not part of the SQL table I used for the report.

Plus, it keeps asking me to enter value for txtPriority. I just
clicked Ok without entering any value and I still have the same
problem of mixed up priority number in my detail line.
James
Nov 13 '05 #9
James, Lets try this! It sounds like you are typing txtPriority =
[PriorityNum] on the second line of the sort/group box and these are both
the sorting/grouping box. Remove what you have on the second line and type
in exactly what Is below (which is probably what you have the control source
of your unbound [PriorityNum] set to).

=IIf([Priority]="Low",1,IIf([Priority]="Medium",2,IIf([Priority]="High",3)))

This should do what you want. If you set this second sort/group to Group
Header=Yes you will see the statement above as the title for the header.

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message

news:<OL********************@comcast.com>...
James, Open your sorting & grouping box. Now select the txtAssignee first from the drop down list then the txtPriority or PriorityNum second. From your post a I can't tell what the control is because of this statement
"txtPriority = [PriorityNum]". It sounds like you are trying to group by [PriorityNum] in you sorting & grouping box, but that field doesn't exist in your recordsource anymore because you renamed it to txtPriority in the
query. Hope this helps, if not post back!

1) First sort and grouping: txtAssignee : in header group 1.
2) Second sort and grouping: txtPriority = [PriorityNum]. This part
is in the header group 2.
PriorityNum = Your IIF statement that you gave me.

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message

news:<Rc********************@comcast.com>...
> James, I believe that is because you are using a reserved word for your > field "Name". Change this to txtName or something else and see if this > doesn't fix your problem. If not post back!
>
> --
> Reggie
>
> ----------
> "James P." <ha*********@yahoo.com> wrote in message
> > "Bradley.-." <sp********@mail.com> wrote in message

news:<A3*******************@news-server.bigpond.net.au>...
> > > "James P." <ha*********@yahoo.com> wrote in message
> > > > Hello,
> > > >
> > > > I have a report with the Priority field is used as sort order and > > > > grouping. The problem is the data in this Priority field if

sorted in
> > > > ascending order is: High, Low, and Medium. How could I sort it
as:
> > > > Low, Medium, High?
> > > >
> > > > Any suggestion is greatly appreciated,
> > > > James
> > >
> > > You are sorting by a text field. Best to use a numeric code to

represent
> > > the levels with a lookup table to get the text, makes sorting

much > > > easier.
> > >
> > > eg.
> > > 1 High
> > > 2 Medium
> > > 3 Low
> >
> > I followed Reggie's suggestion (and also from Bradley, thank you).
> > One thing I did not mentioned before was I have another Name sort and > > grouping over Priority. Which means Name is the main sort and
> > grouping, then Priority (I named it PriorityNum). For debugging
> > purpose, I put PriorityNum (numeric) in both detail line and also in > > the grouping header.
> >
> > Whenever I ran the report, it prompted as: "Enter parameter value:" in > > the box title, and it asked for PriorityNum. I entered nothing. The > > result I got was the Priority numeric printed correctly at the detail > > line, but the grouping header (I put as = [PriorityNum]), it did not > > break correctly. For example, it shows PriorityNum as 2 at the header > > grouping, but in that detail group, it shows all 2, 1 and 3 mixed up. > >
> > Any other suggestions? Thank you in advance.
> > James

Reggie,

In my program, I used txtAssignee instead of Name. Sorry, for the
confusion in my email when I tried to make it simple by using Name
instead of the real field name. So, here is my layout is supposed to
look like:

1) First sort and grouping: txtAssignee : in header group 1.
2) Second sort and grouping: txtPriority = [PriorityNum]. This part
is in the header group 2.
PriorityNum = Your IIF statement that you gave me.

James

Reggie,

I used txtPriority as group header 2. It refers to PriorityNum in the
detail line. PriorityNum is defined with your IIF statement. Neither
PriorityNum nor TxtPriority shown in the dropdown list of Sorting &
Grouping. I guess because these two fields I defined it myself and
they are not part of the SQL table I used for the report.

Plus, it keeps asking me to enter value for txtPriority. I just
clicked Ok without entering any value and I still have the same
problem of mixed up priority number in my detail line.
James

Nov 13 '05 #10
"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message news:<cp********************@comcast.com>...
James, Lets try this! It sounds like you are typing txtPriority =
[PriorityNum] on the second line of the sort/group box and these are both
the sorting/grouping box. Remove what you have on the second line and type
in exactly what Is below (which is probably what you have the control source
of your unbound [PriorityNum] set to).

=IIf([Priority]="Low",1,IIf([Priority]="Medium",2,IIf([Priority]="High",3)))

This should do what you want. If you set this second sort/group to Group
Header=Yes you will see the statement above as the title for the header.

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message

news:<OL********************@comcast.com>...
James, Open your sorting & grouping box. Now select the txtAssignee first from the drop down list then the txtPriority or PriorityNum second. From your post a I can't tell what the control is because of this statement
"txtPriority = [PriorityNum]". It sounds like you are trying to group by [PriorityNum] in you sorting & grouping box, but that field doesn't exist in your recordsource anymore because you renamed it to txtPriority in the
query. Hope this helps, if not post back!
> 1) First sort and grouping: txtAssignee : in header group 1.
> 2) Second sort and grouping: txtPriority = [PriorityNum]. This part
> is in the header group 2.
> PriorityNum = Your IIF statement that you gave me.
--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
> "Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message news:<Rc********************@comcast.com>... > > James, I believe that is because you are using a reserved word for your > > field "Name". Change this to txtName or something else and see if this > > doesn't fix your problem. If not post back!
> >
> > --
> > Reggie
> >
> > ----------
> > "James P." <ha*********@yahoo.com> wrote in message
> > > "Bradley.-." <sp********@mail.com> wrote in message news:<A3*******************@news-server.bigpond.net.au>... > > > > "James P." <ha*********@yahoo.com> wrote in message
> > > > news:f4**************************@posting.google.c om
> > > > > Hello,
> > > > >
> > > > > I have a report with the Priority field is used as sort order and > > > > > grouping. The problem is the data in this Priority field if sorted in > > > > > ascending order is: High, Low, and Medium. How could I sort it
as: > > > > > Low, Medium, High?
> > > > >
> > > > > Any suggestion is greatly appreciated,
> > > > > James
> > > >
> > > > You are sorting by a text field. Best to use a numeric code to represent > > > > the levels with a lookup table to get the text, makes sorting much > > > > easier.
> > > >
> > > > eg.
> > > > 1 High
> > > > 2 Medium
> > > > 3 Low
> > >
> > > I followed Reggie's suggestion (and also from Bradley, thank you).
> > > One thing I did not mentioned before was I have another Name sort and > > > grouping over Priority. Which means Name is the main sort and
> > > grouping, then Priority (I named it PriorityNum). For debugging
> > > purpose, I put PriorityNum (numeric) in both detail line and also in > > > the grouping header.
> > >
> > > Whenever I ran the report, it prompted as: "Enter parameter value:" in > > > the box title, and it asked for PriorityNum. I entered nothing. The > > > result I got was the Priority numeric printed correctly at the detail > > > line, but the grouping header (I put as = [PriorityNum]), it did not > > > break correctly. For example, it shows PriorityNum as 2 at the header > > > grouping, but in that detail group, it shows all 2, 1 and 3 mixed up. > > >
> > > Any other suggestions? Thank you in advance.
> > > James
>
> Reggie,
>
> In my program, I used txtAssignee instead of Name. Sorry, for the
> confusion in my email when I tried to make it simple by using Name
> instead of the real field name. So, here is my layout is supposed to
> look like:
>
> 1) First sort and grouping: txtAssignee : in header group 1.
> 2) Second sort and grouping: txtPriority = [PriorityNum]. This part
> is in the header group 2.
> PriorityNum = Your IIF statement that you gave me.
>
> James

Reggie,

I used txtPriority as group header 2. It refers to PriorityNum in the
detail line. PriorityNum is defined with your IIF statement. Neither
PriorityNum nor TxtPriority shown in the dropdown list of Sorting &
Grouping. I guess because these two fields I defined it myself and
they are not part of the SQL table I used for the report.

Plus, it keeps asking me to enter value for txtPriority. I just
clicked Ok without entering any value and I still have the same
problem of mixed up priority number in my detail line.
James

Reggie,

Yes, it worked now with your last solution. Thank you so much for
helping me for the last several days. Sorry for the confusion. I
know if I could describe the problem clearly in the first place, you
could have showed me at one shot.

Again, thank you for being patient and helping. Wish you're nearby, I
could have bought you a drink.

James
Nov 13 '05 #11
James, No problem. Glad to help any time! Took us a little while but we
got there! Take care!

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message

news:<cp********************@comcast.com>...
James, Lets try this! It sounds like you are typing txtPriority =
[PriorityNum] on the second line of the sort/group box and these are both unbound controls on your report. Instead of that Open your report and open the sorting/grouping box. Remove what you have on the second line and type in exactly what Is below (which is probably what you have the control source of your unbound [PriorityNum] set to).

=IIf([Priority]="Low",1,IIf([Priority]="Medium",2,IIf([Priority]="High",3)))
This should do what you want. If you set this second sort/group to Group Header=Yes you will see the statement above as the title for the header.

--
Reggie

----------
"James P." <ha*********@yahoo.com> wrote in message
"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message

news:<OL********************@comcast.com>...
> James, Open your sorting & grouping box. Now select the txtAssignee
first
> from the drop down list then the txtPriority or PriorityNum second.

From
> your post a I can't tell what the control is because of this
statement > "txtPriority = [PriorityNum]". It sounds like you are trying to group by
> [PriorityNum] in you sorting & grouping box, but that field doesn't

exist in
> your recordsource anymore because you renamed it to txtPriority in
the > query. Hope this helps, if not post back!
>
>
> > 1) First sort and grouping: txtAssignee : in header group 1.
> > 2) Second sort and grouping: txtPriority = [PriorityNum]. This part > > is in the header group 2.
> > PriorityNum = Your IIF statement that you gave me.
>
>
> --
> Reggie
>
> ----------
> "James P." <ha*********@yahoo.com> wrote in message
> > "Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message news:<Rc********************@comcast.com>...
> > > James, I believe that is because you are using a reserved word
for your
> > > field "Name". Change this to txtName or something else and see
if this
> > > doesn't fix your problem. If not post back!
> > >
> > > --
> > > Reggie
> > >
> > > ----------
> > > "James P." <ha*********@yahoo.com> wrote in message
> > > > "Bradley.-." <sp********@mail.com> wrote in message

news:<A3*******************@news-server.bigpond.net.au>...
> > > > > "James P." <ha*********@yahoo.com> wrote in message
> > > > > news:f4**************************@posting.google.c om
> > > > > > Hello,
> > > > > >
> > > > > > I have a report with the Priority field is used as sort
order and
> > > > > > grouping. The problem is the data in this Priority field
if sorted in
> > > > > > ascending order is: High, Low, and Medium. How could I
sort it
as:
> > > > > > Low, Medium, High?
> > > > > >
> > > > > > Any suggestion is greatly appreciated,
> > > > > > James
> > > > >
> > > > > You are sorting by a text field. Best to use a numeric code
to represent
> > > > > the levels with a lookup table to get the text, makes
sorting much
> > > > > easier.
> > > > >
> > > > > eg.
> > > > > 1 High
> > > > > 2 Medium
> > > > > 3 Low
> > > >
> > > > I followed Reggie's suggestion (and also from Bradley, thank
you). > > > > One thing I did not mentioned before was I have another Name sort and
> > > > grouping over Priority. Which means Name is the main sort and
> > > > grouping, then Priority (I named it PriorityNum). For
debugging > > > > purpose, I put PriorityNum (numeric) in both detail line and also in
> > > > the grouping header.
> > > >
> > > > Whenever I ran the report, it prompted as: "Enter parameter

value:" in
> > > > the box title, and it asked for PriorityNum. I entered
nothing. The
> > > > result I got was the Priority numeric printed correctly at the

detail
> > > > line, but the grouping header (I put as = [PriorityNum]), it
did not
> > > > break correctly. For example, it shows PriorityNum as 2 at
> > > > grouping, but in that detail group, it shows all 2, 1 and 3
mixed up.
> > > >
> > > > Any other suggestions? Thank you in advance.
> > > > James
> >
> > Reggie,
> >
> > In my program, I used txtAssignee instead of Name. Sorry, for the
> > confusion in my email when I tried to make it simple by using Name
> > instead of the real field name. So, here is my layout is supposed

to > > look like:
> >
> > 1) First sort and grouping: txtAssignee : in header group 1.
> > 2) Second sort and grouping: txtPriority = [PriorityNum]. This part > > is in the header group 2.
> > PriorityNum = Your IIF statement that you gave me.
> >
> > James

Reggie,

I used txtPriority as group header 2. It refers to PriorityNum in the
detail line. PriorityNum is defined with your IIF statement. Neither
PriorityNum nor TxtPriority shown in the dropdown list of Sorting &
Grouping. I guess because these two fields I defined it myself and
they are not part of the SQL table I used for the report.

Plus, it keeps asking me to enter value for txtPriority. I just
clicked Ok without entering any value and I still have the same
problem of mixed up priority number in my detail line.
James

Reggie,

Yes, it worked now with your last solution. Thank you so much for
helping me for the last several days. Sorry for the confusion. I
know if I could describe the problem clearly in the first place, you
could have showed me at one shot.

Again, thank you for being patient and helping. Wish you're nearby, I
could have bought you a drink.

James

Nov 13 '05 #12

### This discussion thread is closed

Replies have been disabled for this discussion.