Access Error

when I write a query, I get a couple of fileds with #Error because I attempted to divide by zero.

I want to write a query that finds fields with #Error and replaces them with a number.

I have tried the following and none have worked

select iif(x1 like '#Error', 99, x1)
select iif(x1 like "#Error", 99, x1)
select iif(x1 = '#Error', 99, x1)
select iif(x1 = "#Error", 99, x1)
select iif(x1 like '%#%', 99, x1)

any suggestions ??

not completely sure, but i think #Error is not stored anywhere, it's just the indicator that access uses when you've made a booboo

why not use IIF logic to prevent the error? what does your query look like?

rudy

hi r937,

the iff statement is the one that u provided me to calculate the average..

so something like

sum(iif(data1>-1 AND data1<11, data1, 0))/sum(iif(data1>-1 AND data1<11, 1,0))

but the problem here is in some cases, (depending on criteria I specify), if none of the values return true.. I get an error statement..

so in column A
A

99
33
44

since none of these values fall withing the range, the query will return an error as the average, "#Error"

what would be nice is if the #Error is encountered.. I would like it to be replaced with a numer like say 99

you can nest them --

thats awesome..
I tried it and it worked..

thanks a lot r937