Calculations in forms

[Koka_Kola]

Hi, I've just spent ages trying to get an IF function to work on my form on access and keep getting a '#Name?' error. I'm certain that all of the field names I have used are correct, so I'm not really too sure whats wrong with it.

=IF([Fitting]=Yes,"[Carpet Area]*[FittingPrice]+20","0.00")

Does anyone know whats wrong with it?!

Thanks,
Jen

 

[jsv2002]

What type of field is "Fitting" yes/no or text? Also where have you put this, have you created that using an expresion builder as you have an = sign before the if?

Would be better in the afterupdate event procedure of the [Fitting] control might look something like:

If Me.Fitting = True Then

Me.Price = (([Carpet Area]*[Fitting Price])+20)

End if

Assuming that you have a field/control on your form for the total which in this instance I have called price, another would be to do the calculation in a query using the criteria selection for fitting to yes/true depending what type of field it is then someting like Price: (([Carpet Area]*[Fitting Price])+20). This also assumes that the "20" you have is just a surcharge to be added to the end price.

Good luck John

 

[RuralGuy]

Probably because you should use the Immediate IF (IIF) instead of IF. I still see other problems but it should get you a little further along.

 

[jsv2002]

^^^^ Was just about to edit again and say that thanks RG

 

[Koka_Kola]

Fitting is a text field, I didn't use an expression builder - this is an ICT project for school which I'm finding particularly difficult (mainly because I don't have a clue how to use access properly!) So I'm mainly basing it on knowledge of excel if functions!

I'm not sure if I'm allowed to use a query, I think my teacher said that it wasn't neccessary to use them. The further into this project I go, the more lost I become!

I still havn't managed to get it to work. I have got an #error message instead of a #name message now though!

 

[RuralGuy]

Try:
=IIF([Fitting]="Yes",[Carpet Area]*[FittingPrice]+20,0)

 

[Koka_Kola]

Yes!! It works!!

Thank you!

 

[RuralGuy]

That's great! Do you understand why?