Results 1 to 5 of 5
  1. #1
    zipaway is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    53

    Code to insert text in an unbound field if criteria in another table matches

    Hello, I have a table called change_order_dates that contains the following.

    Change_Number Project_Number ShipmentNumber Current_Delivery_Date New_Delivery_Date
    201 1020 1 31/07/2014 31/07/2014
    201 1020 2 07/08/2014 14/08/2014
    202 1020 1 31/07/2014 31/07/2014
    202 1020 2 14/08/2014 14/08/2014

    I also have a report that displays all the change orders for a specific project:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	10 
Size:	4.8 KB 
ID:	17589

    Do you see that unbound New Delivery Date(s) field? I would like it to have the value 'yes' if a new delivery date has been added for that change order. So for the above table, Change order 201 would have a 'yes' but change order 202 would not since non of the dates changed.



    Let me know if there is a way to do this, thanks.

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    I would recommend that you use the query that is the record source of the report. If the report doesn't have a query as its source - now is the time to make one.

    In this query you have a 'calculated field' using an IIF statement which is going to be:

    NewDate: iif([Current_Delivery_Date] = [New_Delivery_Date], "No", "Yes")

    Then in your report this new field NewDate is to be dragged on and added - so you won't be using that unbound field.....

  3. #3
    zipaway is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    53
    Quote Originally Posted by NTC View Post
    I would recommend that you use the query that is the record source of the report. If the report doesn't have a query as its source - now is the time to make one.

    In this query you have a 'calculated field' using an IIF statement which is going to be:

    NewDate: iif([Current_Delivery_Date] = [New_Delivery_Date], "No", "Yes")

    Then in your report this new field NewDate is to be dragged on and added - so you won't be using that unbound field.....
    Thanks for the idea. I tried it out and it works but the query used to make this work shows duplicate change numbers (Different ShipmentNumbers). I would like to somehow have a yes or no for general change order and not each and every ShipmentNumber for each change order.

  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,822
    So if any of the individual records has a Yes then you want Yes for the Change_Order group?

    Do the query calc as NTC suggested but use 0 and 1 (or True and False without quote marks).

    Then need a report group for the ChangeOrder. A textbox named DateChanged in that group header can have expression:

    =Sum([NewDate])

    And in another textbox:
    =IIf([DateChanged]=0, "No", "Yes")

    Can try one expression, not sure it will work:
    =IIf(Sum([NewDate])=0, "No", "Yes")
    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.

  5. #5
    zipaway is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    53
    Thanks you two. I used a variation of your ideas and got it working.

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

Similar Threads

  1. Replies: 6
    Last Post: 06-24-2014, 10:09 PM
  2. Replies: 9
    Last Post: 06-02-2014, 08:10 AM
  3. How to Insert an unbound Form into table
    By fekrinejat in forum Forms
    Replies: 4
    Last Post: 02-04-2013, 10:34 PM
  4. Replies: 1
    Last Post: 01-30-2013, 03:27 PM
  5. Replies: 2
    Last Post: 06-11-2012, 09:37 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