Combining "and" & "or" iif statements

[mlrucci]

Good evening, I have been struggling with creating a calculated field in a table. I am attempting to create a True/False field based on the following criteria.
IIf([PNCName] Like "*L3*") and (IIf([TypeShipInstructionsID] =5 or [TypeShipInstructionsID] =6), True, False))

I keep getting the error "The expression you entered has a function containing the wrong number of arguments. I am attempting to combine an "and" & "or" iif statement.


Suggestions?

[ssanfu]

IMHO, you should NEVER put a calculated FIELD in a table. Tables are for STORING data. The calculation should be in a query.

However, try this:

IIF(([PNCName] Like "*L3*") AND ([TypeShipInstructionsID] = 5 OR [TypeShipInstructionsID] = 6), True, False )

[CJ_London]

for something like this you do not need the iif function, just the formula

[PNCName] Like "*L3*" AND ([TypeShipInstructionsID] = 5 OR [TypeShipInstructionsID] = 6)

[NoellaG]

Iyou should NEVER put a calculated FIELD in a table. Tables are for STORING data. The calculation should be in a query.

2 reasons to use calculated fields:
* if you need to speed up searches on the field and have to put an index on it
* If you need the field as a link field to another table (for example: for import)

[CJ_London]

2 reasons to use calculated fields:
* if you need to speed up searches on the field and have to put an index on it
* If you need the field as a link field to another table (for example: for import)

you can't index a calculated field

[ArviLaanemets]

In case you really need to have calculated value in table, then instead of using formula use VBA script to save the calculated value!

Of-course then you must have AfterUpdate events in all controls linked to fields used as sources in calculation, and those events must recalculate this value whenever any of sources is changed. And when you run some script to update any of those sources, this script must update the calculated value too. All this will be quite a headache - so use this option with caution!

[ssanfu]

I've always gone with Allen Browne's site about Calculated Fields