# Calculated control problem

Tony Williams
Guest
Posts: n/a

 11th Apr 2006
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

=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a

 11th Apr 2006
=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
>
>
>

Tony Williams
Guest
Posts: n/a

 11th Apr 2006
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
>>
>>
>>

=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a

 11th Apr 2006
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
> >>
> >>
> >>

>
>
>

Tony Williams
Guest
Posts: n/a

 11th Apr 2006
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
>> >>
>> >>
>> >>

>>
>>
>>

=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a

 11th Apr 2006
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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

Tony Williams
Guest
Posts: n/a

 11th Apr 2006
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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>

>>
>>
>>

BruceM
Guest
Posts: n/a

 11th Apr 2006
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

"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
>>> >> >>
>>> >> >>
>>> >> >>
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>

>
>

Tony Williams
Guest
Posts: n/a

 11th Apr 2006
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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>

>>
>>
>>

Tony Williams
Guest
Posts: n/a

 14th Apr 2006
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
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?cm9ndWVfYWN0dWFyeQ==?= Microsoft Excel Worksheet Functions 1 6th Mar 2007 10:29 PM Max Moor Microsoft Access Queries 1 22nd Dec 2004 09:19 AM Lynn Trapp Microsoft Access Queries 0 20th Apr 2004 06:49 PM Culbert Microsoft Access Queries 0 22nd Mar 2004 10:48 PM =?Utf-8?B?Sm9obg==?= Microsoft Access Forms 2 14th Jan 2004 02:12 AM

Features