Counting in Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count multiple fields in a report using the following

=Val(IIf(IsNumeric([SPM Housekeeping.Score A]),Count([SPM Housekeeping.Score
A]),0)+IIf(IsNumeric([SPM Housekeeping.Score B]),Count([SPM
Housekeeping.Score B]),0)+IIf(IsNumeric([SPM Housekeeping.Score
C]),Count([SPM Housekeeping.Score C]),0))

When i use the above in a FORM, it works great. For example, if [Score A]
has a value, [Score B] has a value, but [Score C] does not have a value, it
returns 2.

When i plug in the above in a REPORT it gives a number that i can't even
determine how it arrives at that number. Apparently it doesn't work like it
does on a FORM.

I am using MS Access 03, windows XP.

Can someone help, please let me know what i'm doing wrong.

Thanks for your help.
 
I'm not sure you described what you are trying to do...

What section of a report are you attempting to use this expression? If it is
a group or report header or footer, try:
=Sum(Abs(IsNumeric([Score A])) + Abs(IsNumeric([Score B])) +
Abs(IsNumeric([Score C])))

Have you considered normalizing your table structure so you could use a
simple totals query?
 
Hello Duane,

Group footer.

Your expression works, however, i am trying to omit all non-numeric text.
For instance.

I have a rating scale as follows: 1, 2, 3, 4, 5, NA
NA meaning not applicable.

If any item has NA, i want the expression to count only those items that
have a 1 - 5.

Is there a way to fit this into your expression. Thanks for all your help.
--
Rene Lazaro


Duane Hookom said:
I'm not sure you described what you are trying to do...

What section of a report are you attempting to use this expression? If it is
a group or report header or footer, try:
=Sum(Abs(IsNumeric([Score A])) + Abs(IsNumeric([Score B])) +
Abs(IsNumeric([Score C])))

Have you considered normalizing your table structure so you could use a
simple totals query?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
I am trying to count multiple fields in a report using the following

=Val(IIf(IsNumeric([SPM Housekeeping.Score A]),Count([SPM Housekeeping.Score
A]),0)+IIf(IsNumeric([SPM Housekeeping.Score B]),Count([SPM
Housekeeping.Score B]),0)+IIf(IsNumeric([SPM Housekeeping.Score
C]),Count([SPM Housekeeping.Score C]),0))

When i use the above in a FORM, it works great. For example, if [Score A]
has a value, [Score B] has a value, but [Score C] does not have a value, it
returns 2.

When i plug in the above in a REPORT it gives a number that i can't even
determine how it arrives at that number. Apparently it doesn't work like it
does on a FORM.

I am using MS Access 03, windows XP.

Can someone help, please let me know what i'm doing wrong.

Thanks for your help.
Click to expand...
Click to expand...
 
You didn't mention if anything was either wrong or creating an error. Do you
want to add the values across or just count the numeric?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
Hello Duane,

Group footer.

Your expression works, however, i am trying to omit all non-numeric text.
For instance.

I have a rating scale as follows: 1, 2, 3, 4, 5, NA
NA meaning not applicable.

If any item has NA, i want the expression to count only those items that
have a 1 - 5.

Is there a way to fit this into your expression. Thanks for all your help.
--
Rene Lazaro


Duane Hookom said:
I'm not sure you described what you are trying to do...

What section of a report are you attempting to use this expression? If it is
a group or report header or footer, try:
=Sum(Abs(IsNumeric([Score A])) + Abs(IsNumeric([Score B])) +
Abs(IsNumeric([Score C])))

Have you considered normalizing your table structure so you could use a
simple totals query?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
I am trying to count multiple fields in a report using the following

=Val(IIf(IsNumeric([SPM Housekeeping.Score A]),Count([SPM Housekeeping.Score
A]),0)+IIf(IsNumeric([SPM Housekeeping.Score B]),Count([SPM
Housekeeping.Score B]),0)+IIf(IsNumeric([SPM Housekeeping.Score
C]),Count([SPM Housekeeping.Score C]),0))

When i use the above in a FORM, it works great. For example, if [Score A]
has a value, [Score B] has a value, but [Score C] does not have a value, it
returns 2.

When i plug in the above in a REPORT it gives a number that i can't even
determine how it arrives at that number. Apparently it doesn't work like it
does on a FORM.

I am using MS Access 03, windows XP.

Can someone help, please let me know what i'm doing wrong.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
I would like to add the values across but only the numeric, leaving out the
non-numeric such as the NA.

Thanks again.
--
Rene Lazaro


Duane Hookom said:
You didn't mention if anything was either wrong or creating an error. Do you
want to add the values across or just count the numeric?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
Hello Duane,

Group footer.

Your expression works, however, i am trying to omit all non-numeric text.
For instance.

I have a rating scale as follows: 1, 2, 3, 4, 5, NA
NA meaning not applicable.

If any item has NA, i want the expression to count only those items that
have a 1 - 5.

Is there a way to fit this into your expression. Thanks for all your help.
--
Rene Lazaro


Duane Hookom said:
I'm not sure you described what you are trying to do...

What section of a report are you attempting to use this expression? If it is
a group or report header or footer, try:
=Sum(Abs(IsNumeric([Score A])) + Abs(IsNumeric([Score B])) +
Abs(IsNumeric([Score C])))

Have you considered normalizing your table structure so you could use a
simple totals query?

--
Duane Hookom
Microsoft Access MVP


:

I am trying to count multiple fields in a report using the following

=Val(IIf(IsNumeric([SPM Housekeeping.Score A]),Count([SPM Housekeeping.Score
A]),0)+IIf(IsNumeric([SPM Housekeeping.Score B]),Count([SPM
Housekeeping.Score B]),0)+IIf(IsNumeric([SPM Housekeeping.Score
C]),Count([SPM Housekeeping.Score C]),0))

When i use the above in a FORM, it works great. For example, if [Score A]
has a value, [Score B] has a value, but [Score C] does not have a value, it
returns 2.

When i plug in the above in a REPORT it gives a number that i can't even
determine how it arrives at that number. Apparently it doesn't work like it
does on a FORM.

I am using MS Access 03, windows XP.

Can someone help, please let me know what i'm doing wrong.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Try:
=Sum(Val([Score A]) + Val([Score B]) + Val([Score C]))

If this doesn't work, come back with results. Are the fields ever Null?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
I would like to add the values across but only the numeric, leaving out the
non-numeric such as the NA.

Thanks again.
--
Rene Lazaro


Duane Hookom said:
You didn't mention if anything was either wrong or creating an error. Do you
want to add the values across or just count the numeric?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
Hello Duane,

Group footer.

Your expression works, however, i am trying to omit all non-numeric text.
For instance.

I have a rating scale as follows: 1, 2, 3, 4, 5, NA
NA meaning not applicable.

If any item has NA, i want the expression to count only those items that
have a 1 - 5.

Is there a way to fit this into your expression. Thanks for all your help.
--
Rene Lazaro


:

I'm not sure you described what you are trying to do...

What section of a report are you attempting to use this expression? If it is
a group or report header or footer, try:
=Sum(Abs(IsNumeric([Score A])) + Abs(IsNumeric([Score B])) +
Abs(IsNumeric([Score C])))

Have you considered normalizing your table structure so you could use a
simple totals query?

--
Duane Hookom
Microsoft Access MVP


:

I am trying to count multiple fields in a report using the following

=Val(IIf(IsNumeric([SPM Housekeeping.Score A]),Count([SPM Housekeeping.Score
A]),0)+IIf(IsNumeric([SPM Housekeeping.Score B]),Count([SPM
Housekeeping.Score B]),0)+IIf(IsNumeric([SPM Housekeeping.Score
C]),Count([SPM Housekeeping.Score C]),0))

When i use the above in a FORM, it works great. For example, if [Score A]
has a value, [Score B] has a value, but [Score C] does not have a value, it
returns 2.

When i plug in the above in a REPORT it gives a number that i can't even
determine how it arrives at that number. Apparently it doesn't work like it
does on a FORM.

I am using MS Access 03, windows XP.

Can someone help, please let me know what i'm doing wrong.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Hello Duane,

I am trying to count the numeric only. The function you provided works
great, but it pertains to Summing. I want to do the same, but Count instead.

Your help is appreciated!
--
Rene Lazaro


Duane Hookom said:
Try:
=Sum(Val([Score A]) + Val([Score B]) + Val([Score C]))

If this doesn't work, come back with results. Are the fields ever Null?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
I would like to add the values across but only the numeric, leaving out the
non-numeric such as the NA.

Thanks again.
--
Rene Lazaro


Duane Hookom said:
You didn't mention if anything was either wrong or creating an error. Do you
want to add the values across or just count the numeric?

--
Duane Hookom
Microsoft Access MVP


:

Hello Duane,

Group footer.

Your expression works, however, i am trying to omit all non-numeric text.
For instance.

I have a rating scale as follows: 1, 2, 3, 4, 5, NA
NA meaning not applicable.

If any item has NA, i want the expression to count only those items that
have a 1 - 5.

Is there a way to fit this into your expression. Thanks for all your help.
--
Rene Lazaro


:

I'm not sure you described what you are trying to do...

What section of a report are you attempting to use this expression? If it is
a group or report header or footer, try:
=Sum(Abs(IsNumeric([Score A])) + Abs(IsNumeric([Score B])) +
Abs(IsNumeric([Score C])))

Have you considered normalizing your table structure so you could use a
simple totals query?

--
Duane Hookom
Microsoft Access MVP


:

I am trying to count multiple fields in a report using the following

=Val(IIf(IsNumeric([SPM Housekeeping.Score A]),Count([SPM Housekeeping.Score
A]),0)+IIf(IsNumeric([SPM Housekeeping.Score B]),Count([SPM
Housekeeping.Score B]),0)+IIf(IsNumeric([SPM Housekeeping.Score
C]),Count([SPM Housekeeping.Score C]),0))

When i use the above in a FORM, it works great. For example, if [Score A]
has a value, [Score B] has a value, but [Score C] does not have a value, it
returns 2.

When i plug in the above in a REPORT it gives a number that i can't even
determine how it arrives at that number. Apparently it doesn't work like it
does on a FORM.

I am using MS Access 03, windows XP.

Can someone help, please let me know what i'm doing wrong.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Try:
=Sum(IsNull([Score A]) + IsNull([Score B]) + IsNull([Score C])+3)

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
Hello Duane,

I am trying to count the numeric only. The function you provided works
great, but it pertains to Summing. I want to do the same, but Count instead.

Your help is appreciated!
--
Rene Lazaro


Duane Hookom said:
Try:
=Sum(Val([Score A]) + Val([Score B]) + Val([Score C]))

If this doesn't work, come back with results. Are the fields ever Null?

--
Duane Hookom
Microsoft Access MVP


Rene Hernandez said:
I would like to add the values across but only the numeric, leaving out the
non-numeric such as the NA.

Thanks again.
--
Rene Lazaro


:

You didn't mention if anything was either wrong or creating an error. Do you
want to add the values across or just count the numeric?

--
Duane Hookom
Microsoft Access MVP


:

Hello Duane,

Group footer.

Your expression works, however, i am trying to omit all non-numeric text.
For instance.

I have a rating scale as follows: 1, 2, 3, 4, 5, NA
NA meaning not applicable.

If any item has NA, i want the expression to count only those items that
have a 1 - 5.

Is there a way to fit this into your expression. Thanks for all your help.
--
Rene Lazaro


:

I'm not sure you described what you are trying to do...

What section of a report are you attempting to use this expression? If it is
a group or report header or footer, try:
=Sum(Abs(IsNumeric([Score A])) + Abs(IsNumeric([Score B])) +
Abs(IsNumeric([Score C])))

Have you considered normalizing your table structure so you could use a
simple totals query?

--
Duane Hookom
Microsoft Access MVP


:

I am trying to count multiple fields in a report using the following

=Val(IIf(IsNumeric([SPM Housekeeping.Score A]),Count([SPM Housekeeping.Score
A]),0)+IIf(IsNumeric([SPM Housekeeping.Score B]),Count([SPM
Housekeeping.Score B]),0)+IIf(IsNumeric([SPM Housekeeping.Score
C]),Count([SPM Housekeeping.Score C]),0))

When i use the above in a FORM, it works great. For example, if [Score A]
has a value, [Score B] has a value, but [Score C] does not have a value, it
returns 2.

When i plug in the above in a REPORT it gives a number that i can't even
determine how it arrives at that number. Apparently it doesn't work like it
does on a FORM.

I am using MS Access 03, windows XP.

Can someone help, please let me know what i'm doing wrong.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top