if statements in access

[Sonya]

I'm trying to find the average of a row of values. To do
so, I am trying the following SQL code:

select (ifnull(COLA,0)+ifnull(COLB,0)+ifnull(COLC,0)) as
NUMERATOR

and

(if(COLA is NULL,0,1) + if(COLB is NULL,0,1) + if(COLC is
NULL,0,1)) as DENOMINATOR

and divide the two. However, i get a syntax error, so I'm
assuming that access does not support "if" in it's SQL.
Does anyone know if access supports "if" ? If they don't,
does anyone have any work arounds to suggest?

I can't use the avg() function because x + NULL = NULL

 

[Duane Hookom]

You can use Nz(ColA,0)+Nz(ColB,0)+Nz(ColC,0) as Numerator
IsNull(ColA) + IsNull(ColB) + IsNull(ColC) + 3 as Denominator

 

[John Vinson]

I'm trying to find the average of a row of values. To do
so, I am trying the following SQL code:

select (ifnull(COLA,0)+ifnull(COLB,0)+ifnull(COLC,0)) as
NUMERATOR

and

(if(COLA is NULL,0,1) + if(COLB is NULL,0,1) + if(COLC is
NULL,0,1)) as DENOMINATOR

and divide the two. However, i get a syntax error, so I'm
assuming that access does not support "if" in it's SQL.

It doesn't. Use the IIF() function instead. It also doesn't support
the SQL Server specific function ifnull; use the Access function NZ()
in its place.

Don't you love consistancy! Clearly somebody doesn't...

 

[Joe Fallon]

By the way,
Anytime you find yourself writing code like this it is best to take a
timeout and re-examine your database design.
You are calculating "horizontally".
Databases work best when calculated "vertically".
(Excel doesn't care which - but you are "committing spreasheet" in a
database app.)

Other than ease of use you have a HUGE maintainability problem when
structuring things horizontally.
For example: what if your need to include a 4th column in your calculation?
You have to Re-Write every single query and form and code block that relied
on 3 fields only.
OK - so you do it once. Now add a 5th column and a 6th.... oops!

If it was built vertically, you could add as many new records as you want
and NOT CHANGE A THING!
This is why design is so important.