Prev: Current Record Source
Next: Accsess Form
From: Kenj on 21 Jan 2010 23:38
= [LineTotal1]+[Line Total2] throught Line Total25 and Iam getting
sothing only if the entire 25 field have numbers. I think if the fields
are not full it brings back nothing.


From: Stuart McCall on 22 Jan 2010 00:04
"Kenj" <Kenj(a)discussions.microsoft.com> wrote in message
news:3AD8EAB5-0A40-4299-8A9A-84C46AF7A444(a)microsoft.com...
>= [LineTotal1]+[Line Total2] throught Line Total25 and Iam getting
> sothing only if the entire 25 field have numbers. I think if the fields
> are not full it brings back nothing.

That sounds like you are getting Nulls in some of your totals. Try using the
Nz (Null to Zero) function, like this:

=Nz([LineTotal1]) + Nz([Line Total2]) + ...etc.


From: John W. Vinson on 22 Jan 2010 00:34
On Thu, 21 Jan 2010 20:38:02 -0800, Kenj <Kenj(a)discussions.microsoft.com>
wrote:

>= [LineTotal1]+[Line Total2] throught Line Total25 and Iam getting
>sothing only if the entire 25 field have numbers. I think if the fields
>are not full it brings back nothing.
>

That's it. NULL means "this value is unknown, undefined, unspecified". If you
add 1 + 56 + 21 to Unknown, the sum is... unknown.

The NZ() function will return 0 if its argument is NULL:

NZ([linetotal1]) + NZ([linetotal2]) + NZ(...

will meet your needs.

One concern: if this is a Table with 25 LineTotal fields, your tables do not
appear to be correctly normalized!
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Current Record Source
Next: Accsess Form