Counting based on a conditional expression

[Guest]

I am trying to do a count based on a conditional expression in my report, if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
other errors.

Can this be done? Thanks for any help.

 

[Guest]

Hi Tina.
What you could try is:
=Sum (Abs([YourField]="TheConditionYouWantToCount"))
Hope this helps.
Fons

 

[Guest]

Fons, I tried doing this...

=Sum(Abs([Week of]="[Adjusted bill time]<[Productivity Expectation]"))

I received a "data type mismatch" error?????
Also, should it be count instead of sum?

Hi Tina.
What you could try is:
=Sum (Abs([YourField]="TheConditionYouWantToCount"))
Hope this helps.
Fons

I am trying to do a count based on a conditional expression in my report, if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
other errors.

Can this be done? Thanks for any help.

 

[Guest]

Tina,
I would try:
=Sum(ABS([Adjusted bill time]<[Productivity Expectation])
Each time the adjusted BillTime is less than Productivity Expectation this
would result in
=Sum(ABS(-1) and if not =Sum(ABS(0)
The ABS function than changes the -1 to a 1
Than Sum() wil summarize the data for whatever group you set in the query or
report.
Than group your query or report by [Week Of]
Hope this helps.
Fons

Fons, I tried doing this...

=Sum(Abs([Week of]="[Adjusted bill time]<[Productivity Expectation]"))

I received a "data type mismatch" error?????
Also, should it be count instead of sum?

Hi Tina.
What you could try is:
=Sum (Abs([YourField]="TheConditionYouWantToCount"))
Hope this helps.
Fons

I am trying to do a count based on a conditional expression in my report, if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
other errors.

Can this be done? Thanks for any help.

 

[Guest]

Still no luck, I entered =Sum(Abs([Adjusted bill time]<[Productivity
Expectation])) and tried adjusting the number of parenthesis in case that's
the problem. I'm still getting an "overflow" error???

Tina,
I would try:
=Sum(ABS([Adjusted bill time]<[Productivity Expectation])
Each time the adjusted BillTime is less than Productivity Expectation this
would result in
=Sum(ABS(-1) and if not =Sum(ABS(0)
The ABS function than changes the -1 to a 1
Than Sum() wil summarize the data for whatever group you set in the query or
report.
Than group your query or report by [Week Of]
Hope this helps.
Fons

Fons, I tried doing this...

=Sum(Abs([Week of]="[Adjusted bill time]<[Productivity Expectation]"))

I received a "data type mismatch" error?????
Also, should it be count instead of sum?

Hi Tina.
What you could try is:
=Sum (Abs([YourField]="TheConditionYouWantToCount"))
Hope this helps.
Fons

:

I am trying to do a count based on a conditional expression in my report, if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
other errors.

Can this be done? Thanks for any help.

 

[Guest]

I just tried it here, and works fine.
First try it in a new report and place the Sum textbox in the report footer,
that is what I just did.
=Sum(Abs([Adjusted Bill Time]>[Productivity Expectation]))
Make sure the Adj Bill Time and Prod Exp are both formatted for short time.
If it works in a new report, look at your current report and make sure that
the sum textbox is placed in a group or report footer and that the texbox is
unbound.
Hope this helps.
Fons

Still no luck, I entered =Sum(Abs([Adjusted bill time]<[Productivity
Expectation])) and tried adjusting the number of parenthesis in case that's
the problem. I'm still getting an "overflow" error???

Tina,
I would try:
=Sum(ABS([Adjusted bill time]<[Productivity Expectation])
Each time the adjusted BillTime is less than Productivity Expectation this
would result in
=Sum(ABS(-1) and if not =Sum(ABS(0)
The ABS function than changes the -1 to a 1
Than Sum() wil summarize the data for whatever group you set in the query or
report.
Than group your query or report by [Week Of]
Hope this helps.
Fons

Fons, I tried doing this...

=Sum(Abs([Week of]="[Adjusted bill time]<[Productivity Expectation]"))

I received a "data type mismatch" error?????
Also, should it be count instead of sum?

:

Hi Tina.
What you could try is:
=Sum (Abs([YourField]="TheConditionYouWantToCount"))
Hope this helps.
Fons

:

I am trying to do a count based on a conditional expression in my report, if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
other errors.

Can this be done? Thanks for any help.

 

[PC Datasheet]

Put the folloing wxpression in a blank field in your query:
WeekOfMarker:IIF([Adjusted bill time]<[Productivity Expectation],1,0)

Click on the Sigma (looks like E) button in the tolbar at the top of the
screen. Under WeekOfMarker, change Group by to Sum. WeekOfMarker will be a
column of zeroes and ones. Ones mark the records you want to count. By
summing you synthetically get the count of those records.

 

[Guest]

I'm getting close. That did work to give me the 1s and 0s. However, when I
try to sum it, I'm prompted for parameter values.

My Adj bill time field is calculated as follows --> Adjusted bill time:
[TotalBillTime]/([Ttl Sched Hrs
Minutes]-([TotalAnnlLvMinutes]+[TotalSkLvMinutes]+[TotalHolMinutes]+[TtlOflLv
Minutes]))

Is that preventing me from summarizing the WeekOfMarker field?

Put the folloing wxpression in a blank field in your query:
WeekOfMarker:IIF([Adjusted bill time]<[Productivity Expectation],1,0)

Click on the Sigma (looks like E) button in the tolbar at the top of the
screen. Under WeekOfMarker, change Group by to Sum. WeekOfMarker will be a
column of zeroes and ones. Ones mark the records you want to count. By
summing you synthetically get the count of those records.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I am trying to do a count based on a conditional expression in my report, if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
other errors.

Can this be done? Thanks for any help.

 

[Guest]

Tina, are you sure you have the textbox with sum placed in a footer band of
your report?
Fons

I'm getting close. That did work to give me the 1s and 0s. However, when I
try to sum it, I'm prompted for parameter values.

My Adj bill time field is calculated as follows --> Adjusted bill time:
[TotalBillTime]/([Ttl Sched Hrs
Minutes]-([TotalAnnlLvMinutes]+[TotalSkLvMinutes]+[TotalHolMinutes]+[TtlOflLv
Minutes]))

Is that preventing me from summarizing the WeekOfMarker field?

Put the folloing wxpression in a blank field in your query:
WeekOfMarker:IIF([Adjusted bill time]<[Productivity Expectation],1,0)

Click on the Sigma (looks like E) button in the tolbar at the top of the
screen. Under WeekOfMarker, change Group by to Sum. WeekOfMarker will be a
column of zeroes and ones. Ones mark the records you want to count. By
summing you synthetically get the count of those records.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I am trying to do a count based on a conditional expression in my report, if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
other errors.

Can this be done? Thanks for any help.

 

[PC Datasheet]

No! It sounds more like something is misspelled. Look at the parameters you
are being prompted for and are they a misspelling of something else. Post
the parameters you are being prompted for if necessary.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I'm getting close. That did work to give me the 1s and 0s. However, when I
try to sum it, I'm prompted for parameter values.

My Adj bill time field is calculated as follows --> Adjusted bill time:
[TotalBillTime]/([Ttl Sched Hrs
Minutes]-([TotalAnnlLvMinutes]+[TotalSkLvMinutes]+[TotalHolMinutes]+[TtlOflL
v
Minutes]))

Is that preventing me from summarizing the WeekOfMarker field?

Put the folloing wxpression in a blank field in your query:
WeekOfMarker:IIF([Adjusted bill time]<[Productivity Expectation],1,0)

Click on the Sigma (looks like E) button in the tolbar at the top of the
screen. Under WeekOfMarker, change Group by to Sum. WeekOfMarker will be a
column of zeroes and ones. Ones mark the records you want to count. By
summing you synthetically get the count of those records.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I am trying to do a count based on a conditional expression in my

report,
if

that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all

sorts
of

other errors.

Can this be done? Thanks for any help.

 

[Guest]

I'm re-visiting this frustrating thing. I understand what you and Fons are
telling me, but I just cannot get this silly thing to work. And it doesn't
have anything to do with Adjusted Bill Time being an expression?

No! It sounds more like something is misspelled. Look at the parameters you
are being prompted for and are they a misspelling of something else. Post
the parameters you are being prompted for if necessary.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I'm getting close. That did work to give me the 1s and 0s. However, when I
try to sum it, I'm prompted for parameter values.

My Adj bill time field is calculated as follows --> Adjusted bill time:
[TotalBillTime]/([Ttl Sched Hrs
Minutes]-([TotalAnnlLvMinutes]+[TotalSkLvMinutes]+[TotalHolMinutes]+[TtlOflL
v
Minutes]))

Is that preventing me from summarizing the WeekOfMarker field?

Put the folloing wxpression in a blank field in your query:
WeekOfMarker:IIF([Adjusted bill time]<[Productivity Expectation],1,0)

Click on the Sigma (looks like E) button in the tolbar at the top of the
screen. Under WeekOfMarker, change Group by to Sum. WeekOfMarker will be a
column of zeroes and ones. Ones mark the records you want to count. By
summing you synthetically get the count of those records.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com




I am trying to do a count based on a conditional expression in my report,
if
that's possible.

I want to do a count on my "Week of" field if ([Adjusted bill
time]<[Productivity Expectation]). I'm getting "overflow" and all sorts
of
other errors.

Can this be done? Thanks for any help.