> I am selecting data from a dbf via oledb for odbc. What I want to do is
> create a new calculated column similar to this:
> select fldOne, fldTwo / fldThree as CalcCol from xyz
> The problem that I am having is that fldTwo and or fldThree can be zero and
> when I execute the query I get an E_FAIL status error (I am assuming because
> of a div by 0). I have tried an iif(fldTwo = 0 or fldThree = 0, 0 , ,
> fldTwo / fldThree ) statement and it does not work because I think it still
> executes the division even though the statement is true. I know the iif
> statement does work in my sql but only if the division is always valid.
As I understand your scenario, the only case that causes div by 0
error is if fldThree = 0. I just tested this SQL in Access as a Query
on a mock-up of your table:
SELECT fldOne, iif(fldThree = 0, 0, fldTwo / fldThree ) as CalcCol from
It works fine (well, we know that div by 0 does not *really* equal 0,
but that's the result you want to display, and this displays 0 instead
of Error). There's no need to deal w/ fldTwo = 0 in your iif, because 0
divided by anything already computes to 0 with no special attention on
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"
"What's so funny 'bout peace, love & understanding?"
- Nick Lowe