counter on a list of employees

[grace]

Hi,I have a report with a counter. The counter numbers a
list of employees. I would like the employee list to only
display a number if the employee is "Active" (not
terminated) like below:

# Name Status
1 Sue Active
2 John Active
Mary Terminated
Jane Terminated


I set the control source on the counter to: =IIf
([EmployeeStatus]="Terminated",null,1). What happens is
the employee is listed with the same number as the
previous employee like below:

# Name Status
1 Sue Active
2 John Active
2 Mary Terminated
2 Jane Terminated

Can this be done by setting the control source differently?

Thank you.

 

[grace]

Thanks for your help. Unfortunately, that didn't work.
Instead what happened is this:
# Name Status
1 Sue Active
3 John Active
6 Tom Active
6 Mary Terminated
6 Jane Terminated

Not sure where the strange numbering is coming from. Any
ideas?

-----Original Message-----

Hi,I have a report with a counter. The counter numbers a
list of employees. I would like the employee list to only
display a number if the employee is "Active" (not
terminated) like below:

# Name Status
1 Sue Active
2 John Active
Mary Terminated
Jane Terminated


I set the control source on the counter to: =IIf
([EmployeeStatus]="Terminated",null,1). What happens is
the employee is listed with the same number as the
previous employee like below:

# Name Status
1 Sue Active
2 John Active
2 Mary Terminated
2 Jane Terminated

Can this be done by setting the control source

differently?

Make that counter text box invisible and move it an unused
area of the detail section. Then, add another text box with
the expression:

=IIf(EmployeeStatus = "Terminated", Null, countertextbox)

 

[Marshall Barton]

Thanks for your help. Unfortunately, that didn't work.
Instead what happened is this:
# Name Status
1 Sue Active
3 John Active
6 Tom Active
6 Mary Terminated
6 Jane Terminated

Not sure where the strange numbering is coming from. Any
ideas?

You need to set the new text box's Running Sum property to
No.

Regardless of that, you should have had blanks for Mary and
Jane, so there must be something else wrong. Are you sure
you used the full IIf expression?

 

[grace]

Hi again, I really appreciate your help. I quadruple
checked everything and no spelling errors. Ok, what I did
was made the "countertextbox" visible and this is what I
get:
# Name Status CounterTextBox
1 Sue Active 1
2 John Active 1
3 Tom Active 1
3 Mary Terminated
3 Jane Terminated

So, it looks like the visible counter (on the left) is not
recognizing the "null". The Running Sum is set to "Over
Group". [CounterTextBox] is working properly, but if I
set the running sum to "over group" it doesn't recognize
the null. I just don't know what else to check here.
Sorry to drag this on...

Grace

 

[grace]

Well, I think I figured it out. I got rid of
[CounterTextBox] and set the IIf statement to =IIf
([EmployeeStatus]="Terminated","",1). It works! Let me
know if you see any problems with this setup.

Thanks again for your help,
Grace

-----Original Message-----
Hi again, I really appreciate your help. I quadruple
checked everything and no spelling errors. Ok, what I did
was made the "countertextbox" visible and this is what I
get:
# Name Status CounterTextBox
1 Sue Active 1
2 John Active 1
3 Tom Active 1
3 Mary Terminated
3 Jane Terminated

So, it looks like the visible counter (on the left) is not
recognizing the "null". The Running Sum is set to "Over
Group". [CounterTextBox] is working properly, but if I
set the running sum to "over group" it doesn't recognize
the null. I just don't know what else to check here.
Sorry to drag this on...

Grace

-----Original Message-----


It's beginning to look like the IIf condition is not
matching.

=IIf(EmployeeStatus = "Terminated", Null, countertextbox)

Are you sure the EmployeeStatus field really has the string
"Terminated" in it? Did you purchance spell it differently?

This part of your original problem is a very commonplace
thing to do, and if it doesn't work, then you most likely
have made a simple mistake somewhere.

.

 

[Marshall Barton]

Hi again, I really appreciate your help. I quadruple
checked everything and no spelling errors. Ok, what I did
was made the "countertextbox" visible and this is what I
get:
# Name Status CounterTextBox
1 Sue Active 1
2 John Active 1
3 Tom Active 1
3 Mary Terminated
3 Jane Terminated

So, it looks like the visible counter (on the left) is not
recognizing the "null". The Running Sum is set to "Over
Group". [CounterTextBox] is working properly, but if I
set the running sum to "over group" it doesn't recognize
the null. I just don't know what else to check here.
Sorry to drag this on...

I think I've been miscommunicating here. I'll try again in
a different way.

CounterTextBox
Expression: =IIf(EmployeeStatus = "Terminated", 0, 1)
RunningSum: Over Group

# text box
Expression: =IIf(EmployeeStatus = "Terminated", Null,
countertextbox)
RunningSum: No

This should result in:

# Name Status CounterTextBox
1 Sue Active 1
2 John Active 2
3 Tom Active 3
Mary Terminated 3
Jane Terminated 3