I also posted this in the query forum but realized this may actually be the place I should have posted it.
Let me start by saying I'm VERY new to using Access-I've searched the forums but found nothing. I want a field that returns either "Finished Goods, Raw Materials, or Service" dependent on 2 different fields. I thought I got it to work as I get the correct results in the datasheet view of the query; however when I bring this field into my pivot table in excel, it only picks up the first IIF statement-so I have either a blank or "Service". I've tried switching the order and it always picks up the first criteria no matter what it is. Anyone have any light to shed on this?
FG or RM: IIf([dbo_QTY]![Ship Branch Plant]="MSV","Service",IIf([ITEM_BRANCH]![GL Class (B)] Like "F***","Finished Goods",IIf([ITEM_BRANCH]![GL Class (B)] Like "M***","Raw Materials")))