export expressions w zero in denominator

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
Thank you. I tried that and did not get error messages upon export to Excel.
--
Thanks!


[email protected] said:
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.
Click to expand...
Click to expand...
 
Back
Top