#Type! error (1 Viewer)

[MAAS8000]

HELLO,
i have a form like in attached image
field C is calculated field =A/B
I have list box and I am adding records to it
the problem appear when I am trying to retrieve data in list box to the form again .If field "B" is empty in the list box the field "C" appear like in the image #Type!
the problem solved if I enter any data in field B
I tried to make re query event but no effect

 

[CJ_London]

probably because you are trying to divide by null value

try changing your calculation to something like

iif(nz(B,0)=0,null,A/B)

 

[MAAS8000]

I have tried this but same error
=IIf(Nz(;0)=0;Null;[A]/)

 

[CJ_London]

Are A and B the names of your controls? If not, substitute them in the calculation with the real names

 

[theDBguy]

Hi. Pardon me for jumping in but what do you get with this slightly modified version of CJ's code?

=IIf(Nz([B],0)=0,Null,[A]/IIf(Nz([B],0)=0,1,[B]))

or this?

=IIf(Nz([B],0)=0,Null,[A]/Nz([B],1))

PS. I prefer the first one because it handles scenarios where is not empty but a zero (0).

 

[CJ_London]

this

Nz(,0)=0

handles both 0 and null, so returns a null value, however you have reminded me that I seem to recall the iif is evaluated in full so perhaps


iif(nz(B,0)=0,null,nz(A,0)/nz(B))

 

[MAAS8000]

thanks god,
i found the solution in this wonderful forum
=IIf(="";Null;[A]/)

https://access-programmers.co.uk/forums/showthread.php?t=287091

thank you all

 

[isladogs]

That will work where is an empty string but not where it is null.
For that reason you do need to add Nz to handle nulls.

Either one of the earlier solutions or possibly this (if it hasn't already been posted by CJL or DBG)

=IIf(Nz([B],"")="";Null;[A]/[B])