IIF in Form fields

[dazstarr]

Hi

Please can someone help me with an IIF problem.

I have 2 fields on a form:

VATRegistered (Yes/No)
TotalCost (Currency)

If the VATRegistered Field is Yes then I want TotalCost *0.175
if not - then just total cost.

Here is the IIF Statement:

=IIf(-1([Forms]![frmInvoices]![VATRegistered]),[TotalCost]*0.175,[TotalCost])

The bit marked in red is the bit i am struggling with. I have tried swapping it with -1, Yes and True but I just get a syntax error. I have also tried adding = -1, True, Yes after the VATRegistered bit as below:

=IIf([Forms]![frmInvoices]![VATRegistered]=-1),[TotalCost]*0.175,[TotalCost])

Both don't work and I have no idea why. I am entering this IIF statement in the Control Source property of the field in the form.

Please can someone help?

Many thanks in Advance.
Daz

 

[MStef]

Re: IIF in Form Help!

Look at "DemoTotalCostA2000.mdb" (zip).
Open Form1 and try. Look at VBA.

 

[BamaBraves]

=IIf([Forms]![frmInvoices]![VATRegistered]=-1),[TotalCost]*0.175,[TotalCost])

change to

=IIf([Forms]![frmInvoices]![VATRegistered]=-1,[TotalCost]*0.175,[TotalCost])

 

[dazstarr]

That worked a treat!

Many Thanks for your help!!!!

 

[dazstarr]

Re: IIF in Form Help!

Excellent - that works great!

I also got the old IIF statement to work by changing it to:

=IIf([Forms]![frmInvoices]![VATRegistered]=-1,[TotalCost]*0.175,[TotalCost])

Thanks for your help!!

 

[boblarson]

Next time don't post your question more than once.

 

[gemma-the-husky]

although -1 is equivalent to true, personally i wouldnt explicitly use the value -1

simply

=IIf([Forms]![frmInvoices]![VATRegistered]=true),[TotalCost]*0.175,[TotalCost])

is more accurate

 

[dazstarr]

It wasn't intentional

 

[stopher]

=IIf([Forms]![frmInvoices]![VATRegistered]=true),[TotalCost]*0.175,[TotalCost])

or more simply...
=IIf([Forms]![frmInvoices]![VATRegistered],[TotalCost]*0.175,[TotalCost])

Chris

 

[boblarson]

It wasn't intentional

I can understand how a double post can happen in the same category (people do it all of the time because they don't patiently wait for the post to happen when the server is running slow and it just sits there), but yours were in two different categories so it had to be intentional. Now, if you are saying that you meant to post to one category but the first post was in the wrong category, then next time that happens you would use the little red triangle with the black exclamation point to report the post to an administrator/moderator and then they can move the post for you.

 

[dazstarr]

boblarson - thanks for the tip - and it wasnt intentional - just a mistake.

ANYWAY

following on from the initial question:

when using calculating fields on a form - is there a way to write this info back to the tables? I know this isnt strictly good database design but it is something that I need to do.

Please can anyone help.

Thanks in Advance.