List in report by rank

[Guest]

Hello! You were such great help to me yesterday, so I'm back! My new
situation is this...I'm trying to construct a report so that the holders of
various offices print in a list in the appropriate order, such as Chair, Vice
Chair, Secretary, etc. That in itself would not be a problem as all I would
need to do would be to assign a numberical "rank" to each position and use
the rank for the sort order. But here's the kicker...the rest of the list
would be people who don't hold an office. I would like them to print at the
end of the officers. It would look something like:

John Smith, Chair
Mary Jones, Vice Chair
Ella Mentary, Secretary
Member 1
Member 2
Member 2, etc.

The offices are assigned through a [Position] field from a combo box. Those
who do not hold an office have no data in that field. Is there any way I can
assign the rest of the people to a rank without having any entry in the
position field? Does anyone have an idea how to get around this? I would
prefer not to have any label or desctription after the names of the
non-officers. Thanks in advance!

 

[Guest]

What will be your method for assigning ranks for those with value in the
position field?

 

[Guest]

Well, what I got working so far is to use a table PositList with two fields:
a number and a corresponding description, then use that as the row source for
a combo box. When you select "Chair" it is assigned a rank of "1", etc. But
I didn't know how to designate the people who do not have an office. I would
give them the rank of 6 so they would fall underneath the 5 offices, but if I
leave that description of number 6 blank, they end up not having any rank at
all because (I'm assuming) Access sees the lack of a description as no data
at all. Hope this helps.

What will be your method for assigning ranks for those with value in the
position field?

Hello! You were such great help to me yesterday, so I'm back! My new
situation is this...I'm trying to construct a report so that the holders of
various offices print in a list in the appropriate order, such as Chair, Vice
Chair, Secretary, etc. That in itself would not be a problem as all I would
need to do would be to assign a numberical "rank" to each position and use
the rank for the sort order. But here's the kicker...the rest of the list
would be people who don't hold an office. I would like them to print at the
end of the officers. It would look something like:

John Smith, Chair
Mary Jones, Vice Chair
Ella Mentary, Secretary
Member 1
Member 2
Member 2, etc.

The offices are assigned through a [Position] field from a combo box. Those
who do not hold an office have no data in that field. Is there any way I can
assign the rest of the people to a rank without having any entry in the
position field? Does anyone have an idea how to get around this? I would
prefer not to have any label or desctription after the names of the
non-officers. Thanks in advance!

 

[Guest]

Here is something you can do that will take care of the problem. Rather than
having another table to have to keep up, create a function like this and put
it in a standard module:

Function ShowRank(varPosition As Variant) As Long
Select Case varPosition
Case "chair"
ShowRank = 1
Case "vice chair"
ShowRank = 2
Case "secretary"
ShowRank = 3
Case "treasurer"
ShowRank = 4
Case "bouncer"
ShowRank = 5
Case Else
ShowRank = 6
End Select
End Function

Now, base your report on a query. Add a field to the query and in the Field
row put this:

Rank: ShowRank([position])

Where [position] is the name of the position field in your table. It will
assign a number based on the position field. If it is not one of the defined
positions in the list, it will return 6.

Now you can use this new field, Rank, in however you set the order for your
report and it will present them in the order you want.

Be sure you modify the function to get the correct position names


Rank: IIf(IsNull(DLookUp("[monster]","tblMasterActivity","[Mactivity] = '" &
[Mactivity] & "'")),6,1)

Well, what I got working so far is to use a table PositList with two fields:
a number and a corresponding description, then use that as the row source for
a combo box. When you select "Chair" it is assigned a rank of "1", etc. But
I didn't know how to designate the people who do not have an office. I would
give them the rank of 6 so they would fall underneath the 5 offices, but if I
leave that description of number 6 blank, they end up not having any rank at
all because (I'm assuming) Access sees the lack of a description as no data
at all. Hope this helps.

What will be your method for assigning ranks for those with value in the
position field?

Hello! You were such great help to me yesterday, so I'm back! My new
situation is this...I'm trying to construct a report so that the holders of
various offices print in a list in the appropriate order, such as Chair, Vice
Chair, Secretary, etc. That in itself would not be a problem as all I would
need to do would be to assign a numberical "rank" to each position and use
the rank for the sort order. But here's the kicker...the rest of the list
would be people who don't hold an office. I would like them to print at the
end of the officers. It would look something like:

John Smith, Chair
Mary Jones, Vice Chair
Ella Mentary, Secretary
Member 1
Member 2
Member 2, etc.

The offices are assigned through a [Position] field from a combo box. Those
who do not hold an office have no data in that field. Is there any way I can
assign the rest of the people to a rank without having any entry in the
position field? Does anyone have an idea how to get around this? I would
prefer not to have any label or desctription after the names of the
non-officers. Thanks in advance!

 

[Guest]

thank you! I'll be giving it a try...hopefully sometime in what's left of
the day. I'll post back with my results.

susan

Here is something you can do that will take care of the problem. Rather than
having another table to have to keep up, create a function like this and put
it in a standard module:

Function ShowRank(varPosition As Variant) As Long
Select Case varPosition
Case "chair"
ShowRank = 1
Case "vice chair"
ShowRank = 2
Case "secretary"
ShowRank = 3
Case "treasurer"
ShowRank = 4
Case "bouncer"
ShowRank = 5
Case Else
ShowRank = 6
End Select
End Function

Now, base your report on a query. Add a field to the query and in the Field
row put this:

Rank: ShowRank([position])

Where [position] is the name of the position field in your table. It will
assign a number based on the position field. If it is not one of the defined
positions in the list, it will return 6.

Now you can use this new field, Rank, in however you set the order for your
report and it will present them in the order you want.

Be sure you modify the function to get the correct position names


Rank: IIf(IsNull(DLookUp("[monster]","tblMasterActivity","[Mactivity] = '" &
[Mactivity] & "'")),6,1)

Well, what I got working so far is to use a table PositList with two fields:
a number and a corresponding description, then use that as the row source for
a combo box. When you select "Chair" it is assigned a rank of "1", etc. But
I didn't know how to designate the people who do not have an office. I would
give them the rank of 6 so they would fall underneath the 5 offices, but if I
leave that description of number 6 blank, they end up not having any rank at
all because (I'm assuming) Access sees the lack of a description as no data
at all. Hope this helps.

What will be your method for assigning ranks for those with value in the
position field?

:

Hello! You were such great help to me yesterday, so I'm back! My new
situation is this...I'm trying to construct a report so that the holders of
various offices print in a list in the appropriate order, such as Chair, Vice
Chair, Secretary, etc. That in itself would not be a problem as all I would
need to do would be to assign a numberical "rank" to each position and use
the rank for the sort order. But here's the kicker...the rest of the list
would be people who don't hold an office. I would like them to print at the
end of the officers. It would look something like:

John Smith, Chair
Mary Jones, Vice Chair
Ella Mentary, Secretary
Member 1
Member 2
Member 2, etc.

The offices are assigned through a [Position] field from a combo box. Those
who do not hold an office have no data in that field. Is there any way I can
assign the rest of the people to a rank without having any entry in the
position field? Does anyone have an idea how to get around this? I would
prefer not to have any label or desctription after the names of the
non-officers. Thanks in advance!

 

[Guest]

Thank you, thank you, thank you! It worked perfectly! Your directions were
perfect!

Susan

Here is something you can do that will take care of the problem. Rather than
having another table to have to keep up, create a function like this and put
it in a standard module:

Function ShowRank(varPosition As Variant) As Long
Select Case varPosition
Case "chair"
ShowRank = 1
Case "vice chair"
ShowRank = 2
Case "secretary"
ShowRank = 3
Case "treasurer"
ShowRank = 4
Case "bouncer"
ShowRank = 5
Case Else
ShowRank = 6
End Select
End Function

Now, base your report on a query. Add a field to the query and in the Field
row put this:

Rank: ShowRank([position])

Where [position] is the name of the position field in your table. It will
assign a number based on the position field. If it is not one of the defined
positions in the list, it will return 6.

Now you can use this new field, Rank, in however you set the order for your
report and it will present them in the order you want.

Be sure you modify the function to get the correct position names


Rank: IIf(IsNull(DLookUp("[monster]","tblMasterActivity","[Mactivity] = '" &
[Mactivity] & "'")),6,1)

Well, what I got working so far is to use a table PositList with two fields:
a number and a corresponding description, then use that as the row source for
a combo box. When you select "Chair" it is assigned a rank of "1", etc. But
I didn't know how to designate the people who do not have an office. I would
give them the rank of 6 so they would fall underneath the 5 offices, but if I
leave that description of number 6 blank, they end up not having any rank at
all because (I'm assuming) Access sees the lack of a description as no data
at all. Hope this helps.

What will be your method for assigning ranks for those with value in the
position field?

:

Hello! You were such great help to me yesterday, so I'm back! My new
situation is this...I'm trying to construct a report so that the holders of
various offices print in a list in the appropriate order, such as Chair, Vice
Chair, Secretary, etc. That in itself would not be a problem as all I would
need to do would be to assign a numberical "rank" to each position and use
the rank for the sort order. But here's the kicker...the rest of the list
would be people who don't hold an office. I would like them to print at the
end of the officers. It would look something like:

John Smith, Chair
Mary Jones, Vice Chair
Ella Mentary, Secretary
Member 1
Member 2
Member 2, etc.

The offices are assigned through a [Position] field from a combo box. Those
who do not hold an office have no data in that field. Is there any way I can
assign the rest of the people to a rank without having any entry in the
position field? Does anyone have an idea how to get around this? I would
prefer not to have any label or desctription after the names of the
non-officers. Thanks in advance!