Results 1 to 4 of 4
  1. #1
    heidiatmn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    5

    Different Query result in excel pivot table vs Access datasheet view

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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I don't think you need to triple the asterisk.

    [GL Class (B)] LIKE "M*"

    Need value if False for the final IIf.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    heidiatmn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    5
    Quote Originally Posted by June7 View Post
    I don't think you need to triple the asterisk.

    [GL Class (B)] LIKE "M*"

    Need value if False for the final IIf.

    I tried this change-I still get the correct results in the datasheet view in access, but it's still "wrong" in my excel pivot table based on this query. The excel version only seems to be applying the first IIF statement and ignoring the other 2-if tried putting them in different order and it always picks up the first one and ignores the other 2.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You are exporting the query results to Excel? Have you tried a CROSSTAB query in Access?

    I just tested export of a query with an IIf LIKE expression and it worked. Don't know why yours won't.

    If you want to provide db for analysis follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-25-2013, 09:10 AM
  2. export pivot table view to excel
    By sk88 in forum Access
    Replies: 3
    Last Post: 01-27-2012, 01:20 PM
  3. Pivot table - access to excel
    By antagonia in forum Access
    Replies: 3
    Last Post: 12-16-2011, 01:57 PM
  4. Query default to Pivot Table view
    By Theremin_Ohio in forum Access
    Replies: 1
    Last Post: 03-30-2011, 09:17 AM
  5. Export Access Pivot Table to Excel
    By graviz in forum Programming
    Replies: 1
    Last Post: 11-13-2009, 07:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums