removing #error for calculation results when there is a null value

[S Himmelrich]

At current I have a working query column like this:

FY11 CAPEX Variance $: Format([FY11 CAPEX Actual]-[FY11 CAPEX
Forecast],"Currency")

I've tried every variation of isnull and iif and iferror and I get the
same "#ERROR" results with all these combine one way or another.....

Any help would be great.

Scott

 

[John W. Vinson]

At current I have a working query column like this:

FY11 CAPEX Variance $: Format([FY11 CAPEX Actual]-[FY11 CAPEX
Forecast],"Currency")

I've tried every variation of isnull and iif and iferror and I get the
same "#ERROR" results with all these combine one way or another.....

Any help would be great.

Scott

Did you try

Format(NZ([FY 11 CAPEX Actual]) - NZ([FY 11 CAPEX Forecast]), "Currency")?

Are either of these field calculated elsewhere in the same query? If so, you
often cannot reuse a calculated field in a further calculation; you may need
to repeat the calculation in this expression.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

At current I have a working query column like this:

FY11 CAPEX Variance $: Format([FY11 CAPEX Actual]-[FY11 CAPEX
Forecast],"Currency")

I've tried every variation of isnull and iif and iferror and I get the
same "#ERROR" results with all these combine one way or another.....

Any help would be great.

Scott

Did you try

Format(NZ([FY 11 CAPEX Actual]) - NZ([FY 11 CAPEX Forecast]), "Currency")?

Are either of these field calculated elsewhere in the same query? If so, you
often cannot reuse a calculated field in a further calculation; you may need
to repeat the calculation in this expression.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Tried what you suggest - thank you tip on calculated field. Either
calculated or building out another query from the previous calculated
still shows "#ERROR".

Thanks for trying to help - any other suggestions - Scott

 

[John W. Vinson]

Tried what you suggest - thank you tip on calculated field. Either
calculated or building out another query from the previous calculated
still shows "#ERROR".

Thanks for trying to help - any other suggestions - Scott

Please post the complete SQL statement, and indicate the datatypes of the
relevant table fields.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

Please post the complete SQL statement, and indicate the datatypes of the
relevant table fields.
--

I really appreciate your effort......I'm had to move on... This task
was completed in Excel in a matter of seconds.

Here is the solution in Excel:

=IF(ISERR(AQ2-AM2),"", AQ2-AM2)

If you happen to know what I'd code in a access query (design view)
for the above, I'd love to know the syntax that will work - I tried
and still got an "#Error" result when running the query.

Thanks again, I really appriciate your assistance - Take care,
Scott

 

[Access Developer]

Could that possibly be that it was easy because (1) you know Excel and (2)
you had all the information in front of you? In most cases I can imagine,
an Excel solution is useless in an Access database application, and
vice-versa.

Larry Linson
Microsoft Office Access MVP

Please post the complete SQL statement, and indicate the datatypes of the
relevant table fields.
--

I really appreciate your effort......I'm had to move on... This task
was completed in Excel in a matter of seconds.

Here is the solution in Excel:

=IF(ISERR(AQ2-AM2),"", AQ2-AM2)

If you happen to know what I'd code in a access query (design view)
for the above, I'd love to know the syntax that will work - I tried
and still got an "#Error" result when running the query.

Thanks again, I really appriciate your assistance - Take care,
Scott

 

[John W. Vinson]

I really appreciate your effort......I'm had to move on... This task
was completed in Excel in a matter of seconds.

Here is the solution in Excel:

=IF(ISERR(AQ2-AM2),"", AQ2-AM2)

If you happen to know what I'd code in a access query (design view)
for the above, I'd love to know the syntax that will work - I tried
and still got an "#Error" result when running the query.

Thanks again, I really appriciate your assistance - Take care,
Scott

Without knowing the context (the SQL of the query and the nature of the
parameters) there's little I can do to help, other than to point out the
IsError() function.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com