Access 2010 Calculated Data Type Field in a Table Using IIF with EVAL

[JasonTruman]

Hey all

Am I right in asking that you cannot create an IIF statement using the EVAL function with AND for the new Calculated data type field in a table? It's telling me I cannot when I try to save the table design (the usual "Expression is not valid" etc). I'm able to do it in a query though

I thought the whole point of this new calculated field was to make our lives that much easier?

Cheers

Jason

 

[xenou]

Hi,
What is the expression you are trying to use?
ξ

 

[JasonTruman]

IIf(Eval([MYFIELD1]= 1 And [MYFIELD2]>25000),[MYFIELD2]*0.0005,0) where MYFIELD are 2 of my fields from the table. As I said, it works fine in a query and also on a form, it doesn't like it in a Calculated Data Type field in a Table

I would just hope that if MS create a calculated field, you should be to able to perform all Access calculations that you can on a form or in a query

 

[xenou]

Why are you using EVAL()?

 

[JasonTruman]

You have to in Access when using IIF as you cannot evaluate 2 or more expressions as the logical_test without it. It's not like Excel where you use the AND function as the logical_test within the IF function. There is no AND function in Access as it is an Operator, hence why EVAL is used to tell Access to evaluate both expressions

 

[xenou]

Hi,
Try it without EVAL()
ξ

 

[JasonTruman]

I agree that it would accept the formula, but the result is incorrect as you need EVAL for the AND operator (which is NOT a function remember), to work correctly

 

[xenou]

Can you give an example that shows the result is incorrect? It should work. AND is a SQL operator.
ξ

 

[james_lankford]

JasonTruman

go here MS Access: iif Function

scroll to the bottom

why doesn't that work ?