Query by Dividing by 0

[sandy112675]

I am working on a school project which I have been for weeks. I have
one problem that I can't resolve. I have a two tables that I have to
divide to get a percent of free throws made. I have 0 that appear in
the each table and sometimes both of the them have 0. I used the
following query expression:

freethrows: IIf([stat_freetrows_taken]=0,"0%",[stat_freetrows_made]/
[stat_freetrows_taken])

This works but shows that data in decimal form. I have to show this
in % form. I even highlighted it in design view and clicked on
properties to format to percent but everything stays in the decimal
form. How can I get this formatted to percent?

I tried other queries but have been getting the error message because
of divding by 0.

Can you tell me how I can improve my query to format to percent or can
you let me know if there is a better query that I can use to divide
the freetrows made by freetrows taken?

Thanks!


Sandy

 

[tina]

try

Format(IIf([stat_freetrows_taken]=0,0,[stat_freetrows_made]/
[stat_freetrows_taken]), "0%")

to add one decimal place, change it to

Format(IIf([stat_freetrows_taken]=0,0,[stat_freetrows_made]/
[stat_freetrows_taken]), "0.0%")

for more decimal places, add a zero to the right side of the "dot" for each
additional decimal place.

hth

 

[Marshall Barton]

I am working on a school project which I have been for weeks. I have
one problem that I can't resolve. I have a two tables that I have to
divide to get a percent of free throws made. I have 0 that appear in
the each table and sometimes both of the them have 0. I used the
following query expression:

freethrows: IIf([stat_freetrows_taken]=0,"0%",[stat_freetrows_made]/
[stat_freetrows_taken])

This works but shows that data in decimal form. I have to show this
in % form. I even highlighted it in design view and clicked on
properties to format to percent but everything stays in the decimal
form. How can I get this formatted to percent?

Since the result when true is a string, upi can't use a
numeric format. Change the true reslt to a number and it
should work with the percent format:

. . . IIf([stat_freetrows_taken]=0, 0, . . .

 

[sandy112675]

try

Format(IIf([stat_freetrows_taken]=0,0,[stat_freetrows_made]/
[stat_freetrows_taken]), "0%")

to add one decimal place, change it to

Format(IIf([stat_freetrows_taken]=0,0,[stat_freetrows_made]/
[stat_freetrows_taken]), "0.0%")

for more decimal places, add a zero to the right side of the "dot" for each
additional decimal place.

hth

I am working on a school project which I have been for weeks. I have
one problem that I can't resolve. I have a two tables that I have to
divide to get a percent of free throws made. I have 0 that appear in
the each table and sometimes both of the them have 0. I used the
following query expression:

freethrows: IIf([stat_freetrows_taken]=0,"0%",[stat_freetrows_made]/
[stat_freetrows_taken])

This works but shows that data in decimal form. I have to show this
in % form. I even highlighted it in design view and clicked on
properties to format to percent but everything stays in the decimal
form. How can I get this formatted to percent?

I tried other queries but have been getting the error message because
of divding by 0.

Can you tell me how I can improve my query to format to percent or can
you let me know if there is a better query that I can use to divide
the freetrows made by freetrows taken?

Sandy- Hide quoted text -

- Show quoted text -

I tried this and it worked. I just have one more question. How do I
display the expression if I don't want to show the 0% on the query but
I want to show all the one that are more than 0%? Is there anyway
that I can not show the ones that are 0%?

 

[tina]

actually, MVP Marshall Barton's solution is better, because it's simpler;
you don't need to use the Format() function at all. but, recommend you
change the IIf() function because, as posted, it will err when one or more
free throws are taken, but zero are made (bad day!). instead, try the
following expression for the query's calculated field, as

IIf([stat_freetrows_made]=0,0,[stat_freetrows_made]/[stat_freetrows_taken]

to limit the query results to stats greater than zero, set criteria directly
on the [stat_freetrows_made] field as

0

you don't have to show that field in the query output, if you don't want to.

if you're going to display the query results directly to the user (*not* a
good idea), you can set the calculated field's Format property to Percent,
and DecimalPlaces property to 0. better to display results in a form or
report bound to the query; and then you can simply set those two properties
in the control bound to the calculated field.

hth

try

Format(IIf([stat_freetrows_taken]=0,0,[stat_freetrows_made]/
[stat_freetrows_taken]), "0%")

to add one decimal place, change it to

Format(IIf([stat_freetrows_taken]=0,0,[stat_freetrows_made]/
[stat_freetrows_taken]), "0.0%")

for more decimal places, add a zero to the right side of the "dot" for each
additional decimal place.

hth

I am working on a school project which I have been for weeks. I have
one problem that I can't resolve. I have a two tables that I have to
divide to get a percent of free throws made. I have 0 that appear in
the each table and sometimes both of the them have 0. I used the
following query expression:

freethrows: IIf([stat_freetrows_taken]=0,"0%",[stat_freetrows_made]/
[stat_freetrows_taken])

This works but shows that data in decimal form. I have to show this
in % form. I even highlighted it in design view and clicked on
properties to format to percent but everything stays in the decimal
form. How can I get this formatted to percent?

I tried other queries but have been getting the error message because
of divding by 0.

Can you tell me how I can improve my query to format to percent or can
you let me know if there is a better query that I can use to divide
the freetrows made by freetrows taken?

Sandy- Hide quoted text -

- Show quoted text -

I tried this and it worked. I just have one more question. How do I
display the expression if I don't want to show the 0% on the query but
I want to show all the one that are more than 0%? Is there anyway
that I can not show the ones that are 0%?