#Div/0! Message Driving me mad!!!

I have a text box on my form and I keep getting the above error I understand
why I am getting this and that is not a problem, I just need to stop it from
displaying

This is the control source of the text box which is called SubTotal

=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))

Please help!!!!!!!


Thank you all in advance.

S


--
www.sg-consultancy.co.uk

hi,

SG wrote:
I have a text box on my form and I keep getting the above error I understand
why I am getting this and that is not a problem, I just need to stop it from
displaying

This is the control source of the text box which is called SubTotal

=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))
When using IIf, all parameters are evaluated, cause it is a function not
a syntax element like If.

Use

=IIf(IsNull([SubTotal]),
Null,
IIf(Nz(VAT.Column(1),0) = 0,
Null,
CCur(Round([SubTotal]/(VAT.Column(1))))
)
)

mfG
-- stefan --

Hi Stefan,

Thanks for that it worked a treat but has caused another little problem, I
have a text box with the following as the control source
=nz([total],0)-nz([sub total],0) this used to give the the VAT amount i.e.
the difference between the = VAT amount.

Now that I have changed the control source of the sub total text box if
there is nothing in the sub total then the total box diplays in the VAT text
box.

I suppose what I need to happen is if there is null in the Sub total text
box then the VAT text box should also be 0 how can I do this?

Thank you for your help!!



"Stefan Hoffmann" wrote in message
...
hi,

SG wrote:
I have a text box on my form and I keep getting the above error I
understand why I am getting this and that is not a problem, I just need
to stop it from displaying

This is the control source of the text box which is called SubTotal

=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))
When using IIf, all parameters are evaluated, cause it is a function not a
syntax element like If.

Use

=IIf(IsNull([SubTotal]),
Null,
IIf(Nz(VAT.Column(1),0) = 0,
Null,
CCur(Round([SubTotal]/(VAT.Column(1))))
)
)

mfG
-- stefan --

hi,

SG wrote:
I suppose what I need to happen is if there is null in the Sub total text
box then the VAT text box should also be 0 how can I do this?
Use

=IIf(IsNull([sub total]);Null;nz([total],0)-nz([sub total],0))

mfG
-- stefan --

Stefan,

I gett #Name? after setting this as the control source.
=IIf(IsNull([sub total]);Null;nz([total],0)-nz([sub total],0)) as apposed
to =nz([total],0)-nz([sub total],0)


Any IDeas?




"Stefan Hoffmann" wrote in message
...
hi,

SG wrote:
I suppose what I need to happen is if there is null in the Sub total text
box then the VAT text box should also be 0 how can I do this?
Use

mfG
-- stefan --

Stefan,

Thank you for your help!!

All now working as I would like!

Thanks again!



"SG" wrote in message
...
Stefan,

I gett #Name? after setting this as the control source.
=IIf(IsNull([sub total]);Null;nz([total],0)-nz([sub total],0)) as apposed
to =nz([total],0)-nz([sub total],0)


Any IDeas?




"Stefan Hoffmann" wrote in message
...
hi,

SG wrote:
I suppose what I need to happen is if there is null in the Sub total
text box then the VAT text box should also be 0 how can I do this?
Use

mfG
-- stefan --