Prev: Hyperlink within Database 2007
Next: Importing Excel data into Access?
From: kevcar40 on 19 Feb 2010 04:41
Hi
i have a crosstab query that takes values from 2 tables
table one weekly prod scores (entered weekly)
table two takes scrap numbers (entered daily)

the values are divided to create a percentage loss
using
1per:Sum((rejectreason_baseweeklysum.sumof1/
[rejectreason_baseweeklysum Query].wk1))

problem is when the scrap is entered the formula returns 100% because
there is no prod figure

i would like to use an iif statement to check if result is 100% if it
is make it null
ie
1per:iif(Sum((rejectreason_baseweeklysum.sumof1/
[rejectreason_baseweeklysum Query].wk1))= 100, "",
1per:Sum((rejectreason_baseweeklysum.sumof1/
[rejectreason_baseweeklysum Query].wk1))))

is this the correct thing to do
and is my syntax correct

thanks

kevin
From: John Spencer on 19 Feb 2010 08:59
You should probably check for 1 (100%) and not 100 (10,000%) and you should
return Null instead of "" (a zero-length string). If you return the string
then all the values returned for 1per are going to be strings even though the
strings would consist of number characters.

Also, you seem to have an extra closing parenthesis and you should not include
"lper:" inside the expression.

1per:iif(Sum((rejectreason_baseweeklysum.sumof1/
[rejectreason_baseweeklysum Query].wk1))= 1, Null,
Sum((rejectreason_baseweeklysum.sumof1/
[rejectreason_baseweeklysum Query].wk1)))

I'm not sure that the above will give you the expected results, but then I am
not sure what you are trying to calculate.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

kevcar40 wrote:
> Hi
> i have a crosstab query that takes values from 2 tables
> table one weekly prod scores (entered weekly)
> table two takes scrap numbers (entered daily)
>
> the values are divided to create a percentage loss
> using
> 1per:Sum((rejectreason_baseweeklysum.sumof1/
> [rejectreason_baseweeklysum Query].wk1))
>
> problem is when the scrap is entered the formula returns 100% because
> there is no prod figure
>
> i would like to use an iif statement to check if result is 100% if it
> is make it null
> ie
> 1per:iif(Sum((rejectreason_baseweeklysum.sumof1/
> [rejectreason_baseweeklysum Query].wk1))= 100, "",
> 1per:Sum((rejectreason_baseweeklysum.sumof1/
> [rejectreason_baseweeklysum Query].wk1))))
>
> is this the correct thing to do
> and is my syntax correct
>
> thanks
>
> kevin
 | 
Pages: 1
Prev: Hyperlink within Database 2007
Next: Importing Excel data into Access?