#Num and #Div problem (1 Viewer)

[cheloto]

how do i replace "#num!" and "#div/0!" with blanks in my reports?
example:
=([field-1]-[field-2])/[field-2]) empty not #num or #div
where field-1 and field-2 is 0 or empty not #Num! and field-1 is 0 and field-2 is 5 not #Div
=Nz([field-1]-[field-2])/[field-2]);» «) is not working.
Thankful if anyone can see a solution

 

[The_Doc_Man]

To replace the error indicators, prevent them by asking the right question first.

For example,

= IIF( [Field-2] <> 0, ([field-1]-[field-2])/[field-2]), 0 )

 

[cheloto]

Good logical solution, but my Access 2016 does not accept your command. The error message is an invalid indicator or parentheses after constants Zero. I have tried various changes without success. Also instead of <> 0 Is Not Null. The next problem is that both [Field-1] and [Field-2] can be Null (#Num) or [Field-1] Null and [Field-2] >0 (#Div) if you understand what I mean. Thank you for any help.

 

[Gasman]

Good logical solution, but my Access 2016 does not accept your command. The error message is an invalid indicator or parentheses after constants Zero. I have tried various changes without success. Also instead of <> 0 Is Not Null. The next problem is that both [Field-1] and [Field-2] can be Null (#Num) or [Field-1] Null and [Field-2] >0 (#Div) if you understand what I mean. Thank you for any help.

So use the NZ () function on every field with he IIF() function?

 

[cheloto]

sorry not working as I said in my threads.

 

[arnelgp]

=IIF(Nz([Field-2], 0) <> 0 And IsNull([Field-1]) = False, ([field-1]-[field-2])/[field-2], 0)

 

[cheloto]

Thank you arnelpg - this is exactly as I thougt an have tried this multipel times, but get error from my access
Do you know any other way around to replace #errors with blank for the whole database?

 

[Gasman]

I was thinking more along the lines of

=IIF(Nz([Field-2], 0) <> 0, Nz([field-1],0)-NZ([field-2],0))/NZ([field-2],1), 0)

 

[arnelgp]

you can create a Query and use it as recordsource of your report.
add the appropriate Calculated Column:

select *, iif(IsNull([Field-1]) Or IsNull([Field-2]) , Null, [field-1]-[field-2])/[field-2]) As Expr1 From yourTable;

 

[MajP]

you can create a Query and use it as recordsource of your report.
add the appropriate Calculated Column:

Doing it in a query is so much easier to write and debug vs trying to cram it into a calculated control. The query will provide at least some feedback on the error, you get nothing from a calculated control. And it takes forever to troubleshoot. You have to go back and forth between design and form view. Only time IMO ever to do it in the calculated control is if you are in single form view and trying to be more efficient (only resolves the current record).

 

[cheloto]

This code should be correct:
=IIF( [Felt-2] <> 0, ([Felt-1]-[Felt-2])/[Felt-2]), 0 )
but Acces say wrong ( or ( ) or (,) or [ ]
can somebody see where the error is? or is it not possible to make this code
thankful for help

 

[arnelgp]

=IIF( [Felt-2] <> 0, ([Felt-1]-[Felt-2])/[Felt-2], 0 )

 

[The_Doc_Man]

If that is not working, then something else is wrong because that code should be exactly right.

Open your VBA window and from the menu bar, open Tools >> References. In the reference list, see if ANY of the checked items has an annotation of either Missing or Broken. Since IIF is a VBA function, it might be a reference problem. I have seen cases where another item in the list of checked references was not right. The effect was that the VBA function I was using (which would have been defined properly by something else further down the list) became inaccessible because of the implicit order of list evaluation.

I think we have several experienced folks looking at that expression who can't see anything wrong. That means that whatever is wrong is something we can't see (from here.)

 

[cheloto]

Thank you - I will investigate further. It is a simple report where the numbers are catched from a database and the code put directly in the report. All the other data are correct only 0 or Null makes problems.

 

[The_Doc_Man]

Other folks have suggested including NZ into this, and you comment that nulls seem to cause you problems. One way to write this to take nulls into account involves nesting IIFs. Normally I would use CODE brackets but in this case I need to explain how the alternative works by breaking it apart using colors.

IIF( NZ( [Felt-2], 0 ) = 0, 0, IIF( NZ( [Felt-1], 0 ) = 0, -1, ( ( [Felt-1] - [Felt-2] ) / [Felt-2] ) ) )

The first (blue) part tests for Felt-2 to be null or 0. If so, all you can return is 0 (green) since you are about to get a divide-by-zero case - even if Felt-1 is ALSO null or 0. The next (purple) part tests for Felt-1 to be null or 0. If so, what is left is, essentially, a non-zero Felt-2 in the expression ((0-[Felt-2])/[Felt-2]), which is always going to be (red) -1. If you get past both of the NZ tests, you know that you have two non-zero values and can just do the division (orange).

However, the original statement should at least compile correctly. If VBA is calling out punctuation issues on the simple case, then the compiler is not playing with a full deck. If the simple IIF doesn't work, the more complex IIF isn't going to work correctly either.