diff expresions should give same result but don't!

[Mitchell1234]

I've got a table in which there are 20 numeric columns. For one report, I want to add all of the data in those columns together. I used the expression:

Seats: Sum(DailyActivity!PkgSeq01Cnt+DailyActivity!PkgSeq02Cnt+DailyActivity!PkgSeq03Cnt+DailyActivity!PkgSeq04Cnt+DailyActivity!PkgSeq05Cnt+DailyActivity!PkgSeq06Cnt+DailyActivity!PkgSeq07Cnt+DailyActivity!PkgSeq08Cnt+DailyActivity!PkgSeq09Cnt+DailyActivity!PkgSeq10Cnt+DailyActivity!PkgSeq11Cnt+DailyActivity!PkgSeq12Cnt+DailyActivity!PkgSeq13Cnt+DailyActivity!PkgSeq14Cnt+DailyActivity!PkgSeq15Cnt+DailyActivity!PkgSeq16Cnt+DailyActivity!PkgSeq17Cnt+DailyActivity!PkgSeq18Cnt+DailyActivity!PkgSeq19Cnt+DailyActivity!PkgSeq20Cnt)

Where dailyactivity is the name of the table, and pkgseq01cnt is the name of the first column, etc.

The expression returns the number 961.

If i make a query where i sum all of the columns individually, and then a query based off of that query where i add all of the sumed columns together, i get the number 965. (which, as far as i can tell, is the right number)
the expression in the second of those querries is:

Seats: Sum([seat]![SumOfPkgSeq01Cnt]+[seat]![SumOfPkgSeq02Cnt]+[seat]![SumOfPkgSeq03Cnt]+[seat]![SumOfPkgSeq04Cnt]+[seat]![SumOfPkgSeq05Cnt]+[seat]![SumOfPkgSeq06Cnt]+[seat]![SumOfPkgSeq07Cnt]+[seat]![SumOfPkgSeq08Cnt]+[seat]![SumOfPkgSeq09Cnt]+[seat]![SumOfPkgSeq10Cnt]+[seat]![SumOfPkgSeq11Cnt]+[seat]![SumOfPkgSeq12Cnt]+[seat]![SumOfPkgSeq13Cnt]+[seat]![SumOfPkgSeq14Cnt]+[seat]![SumOfPkgSeq15Cnt]+[seat]![SumOfPkgSeq16Cnt]+[seat]![SumOfPkgSeq17Cnt]+[seat]![SumOfPkgSeq18Cnt]+[seat]![SumOfPkgSeq19Cnt]+[seat]![SumOfPkgSeq20Cnt])

where 'seat' is the name of the first query that sums the columns.

I think those two ways should both come up with the same number - but i can't figure out why the first way is missing four! there are thousands of rows so it is hard to go through the table itself. Also, in both of those ways, the querry includes a 'where' column which limits it to rows where another column equals a certain word - but that's relatively straight forward - dont' know why that would make it different.

any ideas?

Thanks! It's driving me crazy!

 

[stopher]

You have most likely got a null value somewhere in your data. Summing using an aggregate query will calculate the totals correctly regardless on nulls. However sum(field1+field2+field3) will return a result that will ignore rows with nulls. You can fix this with sum(nz(field1)+nz(field2)+nz(field3))
The NZ function returns 0 (zero) for null values and hence the expression performs correctly.

If you create a simple query and add a new calculated field to add up all the columns and sort by this new field you will identify all the rows with nulls in them (they will be at the top of the list or bottom depending which way you sorted).

hth
Stopher

 

[boblarson]

Nz doesn't always give you zero. It depends on the context and what you supply for the value of null.

Here's a quote from the Access help file:

If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,