Results 1 to 4 of 4
  1. #1
    Rob1 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Posts
    2

    Report does not show queried IIF result

    Hi all, registered in order to ask this. I've searched around, and haven't found this one anywhere.



    I am running MS Access 2016. Application is part marking in a manufacturing setting. The marking is driven by the customers, so we make the part but have to mark for customer. Customer 1 is different than what Customer 2 needs, for example.

    So, we also have customers who have marking based on who owns the trademark. They bought out a company from a different country, and we made parts for both companies. Now, we are marking parts that went to Customer 2, and selling them to Customer 1, after they bought out Customer 2 (for example).

    The issue here is that the marking has to include something prefixing the marking if it was trademarked by Customer 2, but does not go in if it was trademarked by Customer 1.

    Code:
    IIf([TrademarkOwner]="Customer 2","Cust 2 prefix"," ") & "ALL PREFIX " & [CustomerPN] & " " & [Serial Number Prefix] & "----- " AS PartMark
    This is my query. I have a field in my table for "TrademarkOwner", which calls out either Customer1 or Customer2 (with their actual names). I have a query prompt on the PO number. I have a report built off the query.

    When I run the query, the query results look good, it gives the prefix. When I run the report, I put in the PO as the prompt, and the report does not show the prefix, even though it's pulling from the same query. It just gives me the strings starting at "ALL PREFIX" above.

    Any thoughts? I can't upload the original but I might be able to make a dummy to upload if needed.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Is "Cust 2 prefix" literal text or a field name?

    How is the value for "Customer 2" provided to the query?

    You posted an expression but not an entire query.
    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
    Rob1 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Posts
    2
    That's the language for the query. I can't post the whole query without changing a lot of info for proprietary reasons. I left quotes where they were, I only changed the text within quotes and statements for, again, proprietary reasons. Literal text.

    Value for Customer 1 and Customer 2 are in my marking table along with the PO, so PO1 is for Customer1, PO2 is for Customer2, and there's a field TrademarkOwner with either option. Stored as text.

    I tried storing the field as a number instead (Sandboxed it, so the field name was TMO2 (trademarkowner), and I just put in 1s and 2s, made the statement:

    Code:
    IIf([TMO2]=1,"CustPrefix1 "," ") & "ALL PREFIX" & [CustomerPN] & " " & [Serial Number Prefix] & "----- " AS PartMark

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Exactly what in an SQL statement can be proprietary? Doubt I will be able to help without sample data and full SQL statement because I see nothing wrong with that expression.
    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: 3
    Last Post: 01-04-2015, 03:00 PM
  2. Replies: 4
    Last Post: 04-03-2014, 08:03 AM
  3. Show result whether in two tables or not
    By dr4ke in forum Queries
    Replies: 5
    Last Post: 01-29-2013, 03:03 AM
  4. Query not show result
    By Arsalan in forum Database Design
    Replies: 2
    Last Post: 01-13-2013, 02:57 PM
  5. Replies: 1
    Last Post: 09-28-2011, 09:48 PM

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