Hi,

I am trying to build a logic with IIF...The tblEmployee New has a reconciled column which has the Word NO on it i want to convert it to YES based on the below given logic but its not working....

IIf([tblEmployee New]![Employee_ID] Like '0' & [tblEmployee New]![Dept] Like "Finance" Or "Sales","Yes",[tblEmployee New]![Reconciled])

Also how can i add multiple IIF statements for other employees in Depts like Sales or marketing based on the Employee_ID criteria.

This is not the proper syntax for an IIF statement. Do a search in the VBA editor for IIF to get the syntax.

How are you running this statement?

Also, when you use Like, you have to use a wildcard. So

IIf([tblEmployee New]![Employee_ID] Like '0' & [tblEmployee New]![Dept] Like "Finance" Or "Sales","Yes",[tblEmployee New]![Reconciled])

Should be

IIf([tblEmployee New]![Employee_ID] Like '0*' & [tblEmployee New]![Dept] Like "Finance*" Or Like "Sales*","Yes",[tblEmployee New]![Reconciled])

That is if you want the string to begin with the text, if in the middle, add wildcards to the front and back of the search string.

Did that help any?


mmcdonal

Gotcha....thanks a ton

Actually, the ampersand is concatenation, not AND. You also need to group the AND and OR. Do you want

Condition 1 AND (Condition 2 OR Condition3)

Or do you want

(Condition 1 AND Condition 2) OR Condition3

I assumed the former. Therefore, it would look like

IIf([tblEmployee New]![Employee_ID] Like '0*' AND ([tblEmployee New]![Dept] Like "Finance*" OR [tblEmployee New]![Dept] Like "Sales*"), "Yes", [tblEmployee New]![Reconciled])


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks Greg!!!