IIF in subform

[Andreas]

I have a subform attached to a query which includes some variables defined
inside the query (i.e TotVAT: [Qty]*[Price]).

In my subform I have a boolean variable for VAT. When the checkbox is
checked, I want the value of VAT to be calculated. If not, VAT must be 0. To
do this, I have in my query 2 variables: VAT which is an actual variable
from the table (True/False) and TotVAT which is defined in the query as
explained above.

Then I try to enter the following statement either in the control souce or
in the default value of TotVAT:
= IIF([VAT] = "True", CCur([TotQty]*0,25, 0)
I get error message: You omitted an operator or an operant, .. invalid
character or comma, quotes, double quotes, etc.

I also tried:
= IIF([VAT] = "True", (CCur([TotQty]*25), 0)
= IIF([VAT] = "True", "CCur([TotQty]*25", "0")

but the error is always the same.

What do I have to do?

 

[Douglas J. Steele]

I'm guessing that your regional settings are set to something different from
the North American standard of Decimal symbol of . and List separator of ,.
You're using the same symbol for both in your code.

Usually semi-colon is used as a list separator when the decimal symbol is ,,
so try:

= IIF([VAT] = "True"; CCur([TotQty]*0,25; 0)

 

[Andreas]

You are absolutely correct. I did so and it accepted the statement. However,
it doesn't work as expected. When I press the VAT checkbox, the value of
this field doesn't change.
I even tried it with something more simple, like:
=IIf([VAT]=True;25;0)
or
=IIf([VAT]=Yes;25;0)

I'm guessing that your regional settings are set to something different
from the North American standard of Decimal symbol of . and List separator
of ,. You're using the same symbol for both in your code.

Usually semi-colon is used as a list separator when the decimal symbol is
,, so try:

= IIF([VAT] = "True"; CCur([TotQty]*0,25; 0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a subform attached to a query which includes some variables defined
inside the query (i.e TotVAT: [Qty]*[Price]).

In my subform I have a boolean variable for VAT. When the checkbox is
checked, I want the value of VAT to be calculated. If not, VAT must be 0.
To do this, I have in my query 2 variables: VAT which is an actual
variable from the table (True/False) and TotVAT which is defined in the
query as explained above.

Then I try to enter the following statement either in the control souce
or in the default value of TotVAT:
= IIF([VAT] = "True", CCur([TotQty]*0,25, 0)
I get error message: You omitted an operator or an operant, .. invalid
character or comma, quotes, double quotes, etc.

I also tried:
= IIF([VAT] = "True", (CCur([TotQty]*25), 0)
= IIF([VAT] = "True", "CCur([TotQty]*25", "0")

but the error is always the same.

What do I have to do?

 

[Andreas]

But it works in the control source and also in the initial query so never
mind.

THANKS VERY VERY MUCH FOR THE VERY FAST RESPONSE

You are absolutely correct. I did so and it accepted the statement.
However, it doesn't work as expected. When I press the VAT checkbox, the
value of this field doesn't change.
I even tried it with something more simple, like:
=IIf([VAT]=True;25;0)
or
=IIf([VAT]=Yes;25;0)

I'm guessing that your regional settings are set to something different
from the North American standard of Decimal symbol of . and List
separator of ,. You're using the same symbol for both in your code.

Usually semi-colon is used as a list separator when the decimal symbol is
,, so try:

= IIF([VAT] = "True"; CCur([TotQty]*0,25; 0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a subform attached to a query which includes some variables
defined inside the query (i.e TotVAT: [Qty]*[Price]).

In my subform I have a boolean variable for VAT. When the checkbox is
checked, I want the value of VAT to be calculated. If not, VAT must be
0. To do this, I have in my query 2 variables: VAT which is an actual
variable from the table (True/False) and TotVAT which is defined in the
query as explained above.

Then I try to enter the following statement either in the control souce
or in the default value of TotVAT:
= IIF([VAT] = "True", CCur([TotQty]*0,25, 0)
I get error message: You omitted an operator or an operant, .. invalid
character or comma, quotes, double quotes, etc.

I also tried:
= IIF([VAT] = "True", (CCur([TotQty]*25), 0)
= IIF([VAT] = "True", "CCur([TotQty]*25", "0")

but the error is always the same.

What do I have to do?