Hello,

I have 10 optional numerical fields on a form.
When I try to sum them in the control source of another input box, or
even using VB, I only get a working total if all 10 fields contain a
number.

How do I tell access to ignore fields with no number entered and also
fields containing "#Error?" so that I always get a total in the total
box?

Thanks
Ciar=E1n

On Mon, 21 Jan 2008 07:37:27 -0800 (PST), Cron wrote:

> Hello,
>
> I have 10 optional numerical fields on a form.
> When I try to sum them in the control source of another input box, or
> even using VB, I only get a working total if all 10 fields contain a
> number.
>
> How do I tell access to ignore fields with no number entered and also
> fields containing "#Error?" so that I always get a total in the total
> box?
>
> Thanks
> Ciarán

Look up the Nz function in VBA Help.
=Nz([FieldA]) + Nz([FieldB])+Nz([FieldC]) + etc.....
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Cron wrote:

> Hello,
>
> I have 10 optional numerical fields on a form.
> When I try to sum them in the control source of another input box, or
> even using VB, I only get a working total if all 10 fields contain a
> number.

You probably need to use NZ().
=NZ([fld1],0)+([fld2],0)+([fld3],0)...
>
> How do I tell access to ignore fields with no number entered and also
> fields containing "#Error?" so that I always get a total in the total
> box?

I wouldn't know. Most people would fix the #Error.

>
> Thanks
> Ciarán

Helper
http://www.youtube.com/watch?v=j3Hf_OclE8U

On Jan 21, 4:12 pm, Salad <o... [at] vinegar.com> wrote:
> Cron wrote:
> > Hello,
>
> > I have 10 optional numerical fields on a form.
> > When I try to sum them in the control source of another input box, or
> > even using VB, I only get a working total if all 10 fields contain a
> > number.
>
> You probably need to use NZ().
> =3DNZ([fld1],0)+([fld2],0)+([fld3],0)...

> > How do I tell access to ignore fields with no number entered and also
> > fields containing "#Error?" so that I always get a total in the total
> > box?
>
> I wouldn't know. Most people would fix the #Error.
>

Ha ha the error was actually caused by the same problem somewhere
else! I used an IIf (which I just discovered!) to fix the error and
then the Nz function to fix the totals problem. Thanks a lot for the
help guys!
Ciar=E1n