Results 1 to 4 of 4
  1. #1
    Basket61 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    2

    Angry IIF statement-multiple field results populate another field, all in same table

    I have a table with a list of 3 documents that I need to track as due/received. Once I get all 3 I'd like to populate 1 other field with "all received." However, not all 3 documents are necessarily required.

    In my form, I have the following expression:

    [DocumentsReceived]: IIf([InventoryListReceived]="Yes" And [DRPlan_TestReceived]="Yes" And [VendorContractReceived]="Yes","YES","No")

    [DocumentsReceived] is the field I want to update (I need to be able to run reports based on having all these preliminary docs, so I really need that to populate in a field I can query against going forward).


    I can't find an example of "where" in my expression to tell it to update that particular field.

    Do I need 3 more fields, each telling the database if that document is needed and then increase the expression? Something like:

    [DocumentsReceived]: IIf(([InventoryListRequired] = "Yes" AND [InventoryListReceived]="Yes") And ([DRPlan_TestRequired] = "Yes" AND [DRPlan_TestReceived]="Yes") And ([VendorContractRequired] = "Yes" AND [VendorContractReceived]="Yes"),"YES","No")

    I think I'm doing more work than necessary, but I can't wrap my head around the logic.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No need to save the 'all received' result. This can be calculated when needed in query or on form/report. Filter criteria can be applied to the constructed field.
    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
    Basket61 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    2
    Well, if I'm on a report that I want to produce and send to other people, how do I take this:

    DocumentsReceived]: IIf([InventoryListReceived]="Yes" And [DRPlan_TestReceived]="Yes" And [VendorContractReceived]="Yes","YES","No") and have it show on the report? It would still be a constructed field, such as DocumentsReceived -- how do I display it on the report?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Bind a textbox to the constructed field or put the expression in textbox ControlSource (precede with = sign)

    How are you sending the report? Email attaching PDF?
    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: 1
    Last Post: 10-28-2012, 07:23 PM
  2. Replies: 1
    Last Post: 06-11-2012, 02:34 PM
  3. Multiple field results
    By ronchetc in forum Queries
    Replies: 1
    Last Post: 05-24-2011, 10:03 AM
  4. Replies: 0
    Last Post: 02-12-2011, 01:36 PM
  5. IIF statement to populate field
    By GriffyNJ in forum Queries
    Replies: 0
    Last Post: 10-13-2009, 10:07 AM

Tags for this Thread

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