Default Value in Expression Builder

[AlexTheGr8]

Hi All,



I'm working with an inherited database, and had a question on a change I was trying to make.

There is a table in the database ("Payment Interface") that gets filled as we have new projects to pay. There needs to be a change where if one field (CostCentre) is one of two of the many options, then another field (PaymentArea) needs to change. So for example, if I'm paying out of cost centre 'example1' or 'example2' then the payment code that appears in the table should be '123', instead of the default of '890'.

So I clicked on the field that needs to change, and where the last database designer put in the Default Value space "890", I clicked on the expression builder in default value, and tried to do an IIF statement. My thinking is that IF the criteria for the cost centre is met with either example1 or example2, then give me the value 123, and if not, keep it as the default for all other payments of 890.

My expression looks like: =IIf([CostCentre]="example1" Or "example2",123,890)

I'm getting an error that "the database engine does not recognize either the field 'CostCentre' in a validation expression, or the default value in the table "Payment Interface".

What am I doing wrong? Any ideas are welcome.

Thanks!
Alex

[JoeM]

Firstly, NEVER do calculated fields at the Table level! This violates rules of normalization and is not compatible with any other database program!
I do not know why Microsoft decided to allow this feature in newer versions of Access. It is a bad idea.
On top of that, it is very limited in the kinds of calculations you can do.
And I do not think you can do dependent calculations as default values in table anyway.

In Access, all calculations should be done at the query level. Queries can be used for just about anything that Tables can, so there really is no reason to want to do them at the Table level instead of the Query level.
The calculated field in your query would look like:

PaymentCode: IIf([CostCentre]="example1" Or [CostCentre]="example2",123,890)

So note that "PaymentCode" would NOT be in your table. It is a calculatd field in your query.