IIF Query-different results in pivot table than query datasheet view

[heidiatmn]

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")))

[orange]

The *** is not required. Like "F*" means something begins with F and can be followed by 0 or more characters.

If you want F followed by 3 characters then use Like "F???"

FGorRM: 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",??)))

You need a false condition on the last IIF
You may have to replace the ! with . if these are table fields???
Are you getting any syntax errors?

[heidiatmn]

[QUOTE=orange;164917]The *** is not required. Like "F*" means something begins with F and can be followed by 0 or more characters.

If you want F followed by 3 characters then use Like "F???"

FGorRM: 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",??)))

You need a false condition on the last IIF
You may have to replace the ! with . if these are table fields???
Are you getting any syntax errors?[/QUOTE


Thanks for your help!

I made the changes you suggested with basically the same result. For example, one item number in access datasheet view is showing "Finished Goods" (which I expect as the gl class starts with a F) however when I go to my excel pivot table that is linked to this database I don't get "Finished Goods" for that part number but I get "Check" which is what I put in for your "??" above. I did refresh the pivot table and it did "change" as the prior result was 0, where now I get the "Check".

I do not get a syntax error.