I have a number of calculated controls ona report. If any of the fields
contain a "0" I get #Num!
This is a typical control
=([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
I have tried using the Nz function like this
=NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot],0)
but it doesn't work. I've also tried
=NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]),0)
But get a syntax error with the extra set of brackets.
What am I doing wrong?
Thanks
Tony

=Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
txtClientsTot],1)

Notice the 1 in the last Nz rather than the 0. This will prevent divide by
zero errors and return the value of Nz(([Sum Of
txtClients500],0)*Nz([txtclientstotal]),0).

You need to enclose each field in the Nz function individually. That is
because any value in an math operation that is Null will cause the entire
operation to return Null.

"Tony Williams" wrote:

> I have a number of calculated controls ona report. If any of the fields
> contain a "0" I get #Num!
> This is a typical control
> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
> I have tried using the Nz function like this
> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot],0)
> but it doesn't work. I've also tried
> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]),0)
> But get a syntax error with the extra set of brackets.
> What am I doing wrong?
> Thanks
> Tony
>
>
>

Thanks tried that but Access kept crashing when it came to that page????
Any ideas? I'm using Access 2002
Tony
"Klatuu" <(E-Mail Removed)> wrote in message
news:BFB3A042-E755-450E-A870-(E-Mail Removed)...
> =Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
> txtClientsTot],1)
>
> Notice the 1 in the last Nz rather than the 0. This will prevent divide
> by
> zero errors and return the value of Nz(([Sum Of
> txtClients500],0)*Nz([txtclientstotal]),0).
>
> You need to enclose each field in the Nz function individually. That is
> because any value in an math operation that is Null will cause the entire
> operation to return Null.
>
> "Tony Williams" wrote:
>
>> I have a number of calculated controls ona report. If any of the fields
>> contain a "0" I get #Num!
>> This is a typical control
>> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
>> I have tried using the Nz function like this
>> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot],0)
>> but it doesn't work. I've also tried
>> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]),0)
>> But get a syntax error with the extra set of brackets.
>> What am I doing wrong?
>> Thanks
>> Tony
>>
>>
>>

Sorry, Tony, I posted a syntax error, should be:
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],1)


"Tony Williams" wrote:

> Thanks tried that but Access kept crashing when it came to that page????
> Any ideas? I'm using Access 2002
> Tony
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:BFB3A042-E755-450E-A870-(E-Mail Removed)...
> > =Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
> > txtClientsTot],1)
> >
> > Notice the 1 in the last Nz rather than the 0. This will prevent divide
> > by
> > zero errors and return the value of Nz(([Sum Of
> > txtClients500],0)*Nz([txtclientstotal]),0).
> >
> > You need to enclose each field in the Nz function individually. That is
> > because any value in an math operation that is Null will cause the entire
> > operation to return Null.
> >
> > "Tony Williams" wrote:
> >
> >> I have a number of calculated controls ona report. If any of the fields
> >> contain a "0" I get #Num!
> >> This is a typical control
> >> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
> >> I have tried using the Nz function like this
> >> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot],0)
> >> but it doesn't work. I've also tried
> >> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]),0)
> >> But get a syntax error with the extra set of brackets.
> >> What am I doing wrong?
> >> Thanks
> >> Tony
> >>
> >>
> >>
>
>
>

Sorry but still get #Num! ????
Any ideas?
Tony
"Klatuu" <(E-Mail Removed)> wrote in message
news:B7566AA3-D041-4B98-947D-(E-Mail Removed)...
> Sorry, Tony, I posted a syntax error, should be:
> =(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
> txtClientsTot],1)
>
>
> "Tony Williams" wrote:
>
>> Thanks tried that but Access kept crashing when it came to that page????
>> Any ideas? I'm using Access 2002
>> Tony
>> "Klatuu" <(E-Mail Removed)> wrote in message
>> news:BFB3A042-E755-450E-A870-(E-Mail Removed)...
>> > =Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
>> > txtClientsTot],1)
>> >
>> > Notice the 1 in the last Nz rather than the 0. This will prevent
>> > divide
>> > by
>> > zero errors and return the value of Nz(([Sum Of
>> > txtClients500],0)*Nz([txtclientstotal]),0).
>> >
>> > You need to enclose each field in the Nz function individually. That
>> > is
>> > because any value in an math operation that is Null will cause the
>> > entire
>> > operation to return Null.
>> >
>> > "Tony Williams" wrote:
>> >
>> >> I have a number of calculated controls ona report. If any of the
>> >> fields
>> >> contain a "0" I get #Num!
>> >> This is a typical control
>> >> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
>> >> I have tried using the Nz function like this
>> >> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>> >> txtClientsTot],0)
>> >> but it doesn't work. I've also tried
>> >> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>> >> txtClientsTot]),0)
>> >> But get a syntax error with the extra set of brackets.
>> >> What am I doing wrong?
>> >> Thanks
>> >> Tony
>> >>
>> >>
>> >>
>>
>>
>>

It appears from your post that [txtclientstotal] is a text box control on
your form. What are [Sum Of txtClients500] and [Sum Of txtClientsTot]?
I would suggest qualifying you object. If txtclientstotal is a control,
then use
Me!txtclientstotal
Same with the others if they are controls.


"Tony Williams" wrote:

> Sorry but still get #Num! ????
> Any ideas?
> Tony
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:B7566AA3-D041-4B98-947D-(E-Mail Removed)...
> > Sorry, Tony, I posted a syntax error, should be:
> > =(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
> > txtClientsTot],1)
> >
> >
> > "Tony Williams" wrote:
> >
> >> Thanks tried that but Access kept crashing when it came to that page????
> >> Any ideas? I'm using Access 2002
> >> Tony
> >> "Klatuu" <(E-Mail Removed)> wrote in message
> >> news:BFB3A042-E755-450E-A870-(E-Mail Removed)...
> >> > =Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
> >> > txtClientsTot],1)
> >> >
> >> > Notice the 1 in the last Nz rather than the 0. This will prevent
> >> > divide
> >> > by
> >> > zero errors and return the value of Nz(([Sum Of
> >> > txtClients500],0)*Nz([txtclientstotal]),0).
> >> >
> >> > You need to enclose each field in the Nz function individually. That
> >> > is
> >> > because any value in an math operation that is Null will cause the
> >> > entire
> >> > operation to return Null.
> >> >
> >> > "Tony Williams" wrote:
> >> >
> >> >> I have a number of calculated controls ona report. If any of the
> >> >> fields
> >> >> contain a "0" I get #Num!
> >> >> This is a typical control
> >> >> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
> >> >> I have tried using the Nz function like this
> >> >> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
> >> >> txtClientsTot],0)
> >> >> but it doesn't work. I've also tried
> >> >> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
> >> >> txtClientsTot]),0)
> >> >> But get a syntax error with the extra set of brackets.
> >> >> What am I doing wrong?
> >> >> Thanks
> >> >> Tony
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Sorry I've just realised I've posted this in the form group instead of the
report group so have reposted. Would it make any difference?
Tony
"Klatuu" <(E-Mail Removed)> wrote in message
news:B3793F5D-9B03-4C8A-9BAB-(E-Mail Removed)...
> It appears from your post that [txtclientstotal] is a text box control on
> your form. What are [Sum Of txtClients500] and [Sum Of txtClientsTot]?
> I would suggest qualifying you object. If txtclientstotal is a control,
> then use
> Me!txtclientstotal
> Same with the others if they are controls.
>
>
> "Tony Williams" wrote:
>
>> Sorry but still get #Num! ????
>> Any ideas?
>> Tony
>> "Klatuu" <(E-Mail Removed)> wrote in message
>> news:B7566AA3-D041-4B98-947D-(E-Mail Removed)...
>> > Sorry, Tony, I posted a syntax error, should be:
>> > =(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
>> > txtClientsTot],1)
>> >
>> >
>> > "Tony Williams" wrote:
>> >
>> >> Thanks tried that but Access kept crashing when it came to that
>> >> page????
>> >> Any ideas? I'm using Access 2002
>> >> Tony
>> >> "Klatuu" <(E-Mail Removed)> wrote in message
>> >> news:BFB3A042-E755-450E-A870-(E-Mail Removed)...
>> >> > =Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
>> >> > txtClientsTot],1)
>> >> >
>> >> > Notice the 1 in the last Nz rather than the 0. This will prevent
>> >> > divide
>> >> > by
>> >> > zero errors and return the value of Nz(([Sum Of
>> >> > txtClients500],0)*Nz([txtclientstotal]),0).
>> >> >
>> >> > You need to enclose each field in the Nz function individually.
>> >> > That
>> >> > is
>> >> > because any value in an math operation that is Null will cause the
>> >> > entire
>> >> > operation to return Null.
>> >> >
>> >> > "Tony Williams" wrote:
>> >> >
>> >> >> I have a number of calculated controls ona report. If any of the
>> >> >> fields
>> >> >> contain a "0" I get #Num!
>> >> >> This is a typical control
>> >> >> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
>> >> >> I have tried using the Nz function like this
>> >> >> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>> >> >> txtClientsTot],0)
>> >> >> but it doesn't work. I've also tried
>> >> >> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>> >> >> txtClientsTot]),0)
>> >> >> But get a syntax error with the extra set of brackets.
>> >> >> What am I doing wrong?
>> >> >> Thanks
>> >> >> Tony
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

As I mentioned in the other group, you are receiving assistance here. It
doesn't matter that it is not the ideal group. Best to stay with this
thread.

"Tony Williams" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sorry I've just realised I've posted this in the form group instead of the
> report group so have reposted. Would it make any difference?
> Tony
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:B3793F5D-9B03-4C8A-9BAB-(E-Mail Removed)...
>> It appears from your post that [txtclientstotal] is a text box control on
>> your form. What are [Sum Of txtClients500] and [Sum Of txtClientsTot]?
>> I would suggest qualifying you object. If txtclientstotal is a control,
>> then use
>> Me!txtclientstotal
>> Same with the others if they are controls.
>>
>>
>> "Tony Williams" wrote:
>>
>>> Sorry but still get #Num! ????
>>> Any ideas?
>>> Tony
>>> "Klatuu" <(E-Mail Removed)> wrote in message
>>> news:B7566AA3-D041-4B98-947D-(E-Mail Removed)...
>>> > Sorry, Tony, I posted a syntax error, should be:
>>> > =(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
>>> > txtClientsTot],1)
>>> >
>>> >
>>> > "Tony Williams" wrote:
>>> >
>>> >> Thanks tried that but Access kept crashing when it came to that
>>> >> page????
>>> >> Any ideas? I'm using Access 2002
>>> >> Tony
>>> >> "Klatuu" <(E-Mail Removed)> wrote in message
>>> >> news:BFB3A042-E755-450E-A870-(E-Mail Removed)...
>>> >> > =Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
>>> >> > txtClientsTot],1)
>>> >> >
>>> >> > Notice the 1 in the last Nz rather than the 0. This will prevent
>>> >> > divide
>>> >> > by
>>> >> > zero errors and return the value of Nz(([Sum Of
>>> >> > txtClients500],0)*Nz([txtclientstotal]),0).
>>> >> >
>>> >> > You need to enclose each field in the Nz function individually.
>>> >> > That
>>> >> > is
>>> >> > because any value in an math operation that is Null will cause the
>>> >> > entire
>>> >> > operation to return Null.
>>> >> >
>>> >> > "Tony Williams" wrote:
>>> >> >
>>> >> >> I have a number of calculated controls ona report. If any of the
>>> >> >> fields
>>> >> >> contain a "0" I get #Num!
>>> >> >> This is a typical control
>>> >> >> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
>>> >> >> I have tried using the Nz function like this
>>> >> >> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>>> >> >> txtClientsTot],0)
>>> >> >> but it doesn't work. I've also tried
>>> >> >> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>>> >> >> txtClientsTot]),0)
>>> >> >> But get a syntax error with the extra set of brackets.
>>> >> >> What am I doing wrong?
>>> >> >> Thanks
>>> >> >> Tony
>>> >> >>
>>> >> >>
>>> >> >>
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>

Yes they are all controls which derive from a query which is the control
source of the report. Do I put the Me! infront of every field in the formula
that you gave me earlier?
Thanks
Tony
"Klatuu" <(E-Mail Removed)> wrote in message
news:B3793F5D-9B03-4C8A-9BAB-(E-Mail Removed)...
> It appears from your post that [txtclientstotal] is a text box control on
> your form. What are [Sum Of txtClients500] and [Sum Of txtClientsTot]?
> I would suggest qualifying you object. If txtclientstotal is a control,
> then use
> Me!txtclientstotal
> Same with the others if they are controls.
>
>
> "Tony Williams" wrote:
>
>> Sorry but still get #Num! ????
>> Any ideas?
>> Tony
>> "Klatuu" <(E-Mail Removed)> wrote in message
>> news:B7566AA3-D041-4B98-947D-(E-Mail Removed)...
>> > Sorry, Tony, I posted a syntax error, should be:
>> > =(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
>> > txtClientsTot],1)
>> >
>> >
>> > "Tony Williams" wrote:
>> >
>> >> Thanks tried that but Access kept crashing when it came to that
>> >> page????
>> >> Any ideas? I'm using Access 2002
>> >> Tony
>> >> "Klatuu" <(E-Mail Removed)> wrote in message
>> >> news:BFB3A042-E755-450E-A870-(E-Mail Removed)...
>> >> > =Nz(([Sum Of txtClients500],0)*Nz([txtclientstotal]),0)/Nz([Sum Of
>> >> > txtClientsTot],1)
>> >> >
>> >> > Notice the 1 in the last Nz rather than the 0. This will prevent
>> >> > divide
>> >> > by
>> >> > zero errors and return the value of Nz(([Sum Of
>> >> > txtClients500],0)*Nz([txtclientstotal]),0).
>> >> >
>> >> > You need to enclose each field in the Nz function individually.
>> >> > That
>> >> > is
>> >> > because any value in an math operation that is Null will cause the
>> >> > entire
>> >> > operation to return Null.
>> >> >
>> >> > "Tony Williams" wrote:
>> >> >
>> >> >> I have a number of calculated controls ona report. If any of the
>> >> >> fields
>> >> >> contain a "0" I get #Num!
>> >> >> This is a typical control
>> >> >> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
>> >> >> I have tried using the Nz function like this
>> >> >> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>> >> >> txtClientsTot],0)
>> >> >> but it doesn't work. I've also tried
>> >> >> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of
>> >> >> txtClientsTot]),0)
>> >> >> But get a syntax error with the extra set of brackets.
>> >> >> What am I doing wrong?
>> >> >> Thanks
>> >> >> Tony
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Still having trouble here I've tried the following subsequently
=((Nz([Sum Of
txtStocktot],0)-Nz([txtprevqtrtotal18],0))/Nz([txtprevqtrtotal18],1))*100
still gives me #Num!
and this
=((Nz([Me!Sum Of
txtStocktot],0)-Nz([Me!txtprevqtrtotal18],0))/Nz([Me!txtprevqtrtotal18],1))*100
when the report is run asks me for the value of all the controls starting
Me! and on the rpeort I get #Name

Can anyone help here?
Thanks
Tony
"Tony Williams" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a number of calculated controls ona report. If any of the fields
>contain a "0" I get #Num!
> This is a typical control
> =([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]
> I have tried using the Nz function like this
> =NZ([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot],0)
> but it doesn't work. I've also tried
> =NZ(([Sum Of txtClients500]*[txtclientstotal])/[Sum Of txtClientsTot]),0)
> But get a syntax error with the extra set of brackets.
> What am I doing wrong?
> Thanks
> Tony
>