|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 7th, 2007, 04:58 PM
posted to microsoft.public.access.reports
|
|||
|
|||
Counting in Report
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 Lazaro 
|
|
#2
August 7th, 2007, 08:46 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Counting in Report
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" wrote: 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 Lazaro
|
|
#3
August 7th, 2007, 09:32 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Counting in Report
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" wrote: 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" wrote: 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 Lazaro
|
|
#4
August 7th, 2007, 10:18 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Counting in Report
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" wrote: 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" wrote: 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" wrote: 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 Lazaro
|
|
#5
August 7th, 2007, 10:24 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Counting in Report
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 Lazaro
|
|
#6
August 7th, 2007, 10:44 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Counting in Report
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 Lazaro
|
|
#7
August 14th, 2007, 09:52 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Counting in Report
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 Lazaro
|
|
#8
August 14th, 2007, 10:32 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Counting in Report
Try:
=Sum(IsNull([Score A]) + IsNull([Score B]) + IsNull([Score C])+3) -- Duane Hookom Microsoft Access MVP "Rene Hernandez" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 Lazaro
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
