Horizontal Totals (Follow-Up)

  • Thread starter Thread starter Jaycee
  • Start date Start date
J

Jaycee

This is a follow-up to a previous question re totalling
horizontally in a report. Forgive the un-normality of my
structure.

I have the following expression in the Totals box:

=Nz([CIA],0)+Nz([CPA],0)+Nz([CISA],0)+Nz([CFE],0)+Nz
([CNA],0)+Nz([CM],0)+Nz([CLP],0)+Nz([OtherCert],0)

It works fine for all but 3 of 28 records. The three
records that have both CIA and CPA values returns
#Error. Other records that have either of these alone or
in combination with other values (besides each other)
return a correct total.

What is it about the CIA/CPA combination that produces
#Error?

Thanks,

Jaycee
 
Jaycee

Perhaps the exact values in those fields when it "breaks" would be useful
information in troubleshooting? And an example of the values that DO
work...

More info, please...

Jeff Boyce
<Access MVP>
 
Jaycee

You are describing a report layout. There's no (absolute) connection
between what is stored and what is displayed in a report.

Another data structure would still let you determine the number of
certifications -- as you alluded to in mentioning the "un-normality" of your
data. Have you considered biting the bullet and normalizing your data? It
would make your reporting much easier, as Access is designed to (help you)
report normalized data, not "spreadsheet" data.

Take a look at HELP information on "crosstab queries" -- this may give you
new ideas for displaying all categories (again, though, works for normalized
data).

Consider my previous response -- examine the actual values/data in valid and
the invalid fields, one-by-one, rather than as concatenated.
 
Back
Top