[RESOLVED] Null result

[shadow314us]

Hello all,

I'm trying run a query to do some calculation. If field2 and field3 have zero value it throws an exception. Can anyone kindly give some insight as how to work around.

sum(1)*MAX(field2)/MAX(field3)

thanks,

[szlamany]

Most aggregate functions cannot deal with NULL - and NULL represents and infinite value and can not be "added" or "maxed" with other real values.

If this is MS SQL use the ISNULL function to transform your null values.

sum(1)*MAX(IsNull(field2,0))/MAX(IsNull(field3,0))

If this is ACCESS then I think there is a NZ() function that does something similar. It's always helpful to tell us what DB you are using.

[shadow314us]

I'm trying to run the query in access database.

[szlamany]

Then lookup how to use NZ() - I'm not an access person but I believe that NZ() does the same thing as ISNULL().

[shadow314us]

select sum(1) *MAX( Nz((table1.total),1))/MAX(Nz((table1.time),1)) as cal,machine from table1 group by field2

tried Nz function, but i still get error as i'm dividing zero by zero in the calculation as i have a record where table1.total and table1.time has value 0.

kindly give some insight.

[szlamany]

First - I don't understand why you are using NZ() to turn the blank/null value into a 1 - shouldn't you be turning it into 0??

Maybe something like this:

select IIF(MAX(Nz(table1.time,0)) =0,0,sum(1) *MAX( Nz(table1.total,0))/MAX(Nz(table1.time,0)) )

IIF() can test the divisor value to see if it's 0.

[shadow314us]

thanks.. it worked.