Calculated control problem

T

Tony Williams

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
 
K

Klatuu

=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.
 
T

Tony Williams

Thanks tried that but Access kept crashing when it came to that page????
Any ideas? I'm using Access 2002
Tony
Klatuu said:
=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 said:
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
Click to expand...
Click to expand...
 
K

Klatuu

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


Tony Williams said:
Thanks tried that but Access kept crashing when it came to that page????
Any ideas? I'm using Access 2002
Tony
Klatuu said:
=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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Sorry but still get #Num! ????
Any ideas?
Tony
Klatuu said:
Sorry, Tony, I posted a syntax error, should be:
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],1)


Tony Williams said:
Thanks tried that but Access kept crashing when it came to that page????
Any ideas? I'm using Access 2002
Tony
Klatuu said:
=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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

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 said:
Sorry but still get #Num! ????
Any ideas?
Tony
Klatuu said:
Sorry, Tony, I posted a syntax error, should be:
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],1)


Tony Williams said:
Thanks tried that but Access kept crashing when it came to that page????
Any ideas? I'm using Access 2002
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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

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 said:
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 said:
Sorry but still get #Num! ????
Any ideas?
Tony
Klatuu said:
Sorry, Tony, I posted a syntax error, should be:
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],1)


:

Thanks tried that but Access kept crashing when it came to that
page????
Any ideas? I'm using Access 2002
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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

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 said:
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 said:
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 said:
Sorry but still get #Num! ????
Any ideas?
Tony
Sorry, Tony, I posted a syntax error, should be:
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],1)


:

Thanks tried that but Access kept crashing when it came to that
page????
Any ideas? I'm using Access 2002
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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

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 said:
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 said:
Sorry but still get #Num! ????
Any ideas?
Tony
Klatuu said:
Sorry, Tony, I posted a syntax error, should be:
=(Nz([Sum Of txtClients500],0)*Nz([txtclientstotal],0))/Nz([Sum Of
txtClientsTot],1)


:

Thanks tried that but Access kept crashing when it came to that
page????
Any ideas? I'm using Access 2002
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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

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
 
T

Tony Williams

Have solved my problem using Iif like this

Iif([txtprevqtrtotal18]=0,"0",(([Sum Of
txtStocktot]-[txtprevqtrtotal18)/[txtprevqtrtotal18])*100)

and that seems to work Ok

Incidently realised in previous post the Me! should have been outside
bracket.

Thanks to everyone for their help
Tony

Tony Williams said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Zero's in reports 5
#Num question 2
How do I get rid of #Num! 7
My warning message box wont work on calculated contols 4
Adding two subtotals together. 2
Comparing calculated controls on a tab control pages 6
Odd answer to calculation 3
Unbound Calculation populates from 3 seperate fields 1

Top