export expressions w zero in denominator

[chrissyb]

Hi... it seems that Access is automatically deleting my expression values
that yield an undefined value (zero in the denominator) when I export to
Excel. I get a message that x number or records have been deleted. It
appears that there are now blank spaces in Excel where zeros and #Div/0! (or
#Value) were in Access.

Is this correct? Is this how Access handles exporting expressions with
undefined, zero in the denominator results? If so I'm ok with that as I have
to delete them eventually anyway. I just want to be sure this isn't messing
up my data and calculated expressions.

Also when I run a query with undefined values in it, I can get results.
However if I try to open that query at a later time I get one blank row of
data returned with a divide zero message. So, it seems useless to save
queries that have records of expressions with zero in the denominator. I
have to re-input the query time after time. Is this expected?



Please advise.

 

[j.mapson.nurick]

Hi Chrissy,

I'd tackle this sort of thing by explicitly handling the error
condition in the expression(s) in the query. e.g. instead of

[Numerator]/[Denominator]

I'd use this:

IIf([Denominator]<>0, [Numerator]/[Denominator], Null)

 

[learningaccess]

Thank you. I tried that and did not get error messages upon export to Excel.
--
Thanks!

Hi Chrissy,

I'd tackle this sort of thing by explicitly handling the error
condition in the expression(s) in the query. e.g. instead of

[Numerator]/[Denominator]

I'd use this:

IIf([Denominator]<>0, [Numerator]/[Denominator], Null)

Hi... it seems that Access is automatically deleting my expression values
that yield an undefined value (zero in the denominator) when I export to
Excel. I get a message that x number or records have been deleted. It
appears that there are now blank spaces in Excel where zeros and #Div/0! (or
#Value) were in Access.

Is this correct? Is this how Access handles exporting expressions with
undefined, zero in the denominator results? If so I'm ok with that as I have
to delete them eventually anyway. I just want to be sure this isn't messing
up my data and calculated expressions.

Also when I run a query with undefined values in it, I can get results.
However if I try to open that query at a later time I get one blank row of
data returned with a divide zero message. So, it seems useless to save
queries that have records of expressions with zero in the denominator. I
have to re-input the query time after time. Is this expected?



Please advise.