Count

[Dale]

My Table (TblServiceOrders) has a Text Field (Status) that has these options:
Billed, To Be Billed, Completed, Open. I'm trying to count off of this field
in my report footer that would give the the total "Billed", total "Open",
etc. service orders.

I tried this formulas but my result was a "0" answer.
=Sum(Abs([Status]="Billed"))

Any advise?

 

[fredg]

My Table (TblServiceOrders) has a Text Field (Status) that has these options:
Billed, To Be Billed, Completed, Open. I'm trying to count off of this field
in my report footer that would give the the total "Billed", total "Open",
etc. service orders.

I tried this formulas but my result was a "0" answer.
=Sum(Abs([Status]="Billed"))

Any advise?

You wish to count how many records are 'Billed', etc.
Use unbound text controls.

Try:
=Sum(Iif([Status] = "Billed",1,0))
=Sum(Iif([Status] = "Open",1,0))
etc.

However, if [Status] is really a lookup field, then it's actual value
is going to be a Number, not text.
Assuming the actual value stored in [Status] that represents "Billed"
is 2, then

=Sum(Iif([Status] =2,1,0))
etc.

 

[Duane Hookom]

I would not assume there will always be only these options. I would use a
subreport with a totals query as its Record Source. This will automatically
include any new status that might get added in the future.

 

[Dale]

Thanks for the replies - I tried the formula below. It's not a look up field
but rather just text imported from excel into my table. There must be
something wrong with the data I'm importing though becuase I'm still getting
a "0" answer. It's like it doesn't see "Billed".
--
Dale

My Table (TblServiceOrders) has a Text Field (Status) that has these options:
Billed, To Be Billed, Completed, Open. I'm trying to count off of this field
in my report footer that would give the the total "Billed", total "Open",
etc. service orders.

I tried this formulas but my result was a "0" answer.
=Sum(Abs([Status]="Billed"))

Any advise?

You wish to count how many records are 'Billed', etc.
Use unbound text controls.

Try:
=Sum(Iif([Status] = "Billed",1,0))
=Sum(Iif([Status] = "Open",1,0))
etc.

However, if [Status] is really a lookup field, then it's actual value
is going to be a Number, not text.
Assuming the actual value stored in [Status] that represents "Billed"
is 2, then

=Sum(Iif([Status] =2,1,0))
etc.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.