Counting in Report

[Rene Hernandez]

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.

 

[Duane Hookom]

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?

 

[Rene Hernandez]

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.

 

[Duane Hookom]

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.

 

[Rene Hernandez]

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.

 

[Duane Hookom]

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

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.

 

[Rene Hernandez]

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

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

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.

 

[Duane Hookom]

Try:
=Sum(IsNull([Score A]) + IsNull([Score B]) + IsNull([Score C])+3)

--
Duane Hookom
Microsoft Access MVP

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

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

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.