Results 1 to 10 of 10
  1. #1
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62

    subtract days from date using user defined number in reference table

    I am building a Database and here is my question/issue:


    I have table1 called [Personnel]. This table holds "EmployeeID", "Name", "ReturnDate", ETC
    I have table2 called [Awards]. This table holds: "EmployeeID", "Name", "ReturnDate", "Recommender","TypeOfAward" "Award1SubmissionDate", "Award2SubmissionDate", and "Award3SubmissionDate"
    Data will be entered into table1 that is then updated to table2 using an appendQuery. The data that is appended is ("EmployeeID", "Name", "ReturnDate") YES I know the data is redundant but I need it that way.
    Both table1 and table2 will have data entered via septerate forms. Table2 ("EmployeeID", "Name", "ReturnDate") will not be allow updates from table2Form bc i have it locked and it is being pulled from table1.
    I have table3 called [Settings]. This table holds "Title" and "Key" (that need to be referenced via I am thinking DLookup?) Table3 looks like this:
    Title Column has Award1, award2, award3. the Key Column has the number of days that need to be subtracted from the ReturnDate in table2 that has been appended from table1 so award1 would be 100, award2 would be 50 and award3 would be 30
    What I am looking to do is I need "Award1SubmissionDate", "Award2SubmissionDate", and "Award3SubmissionDate" in table2 to be auto populated based on Return Date MINUS the number in the key column for that type of award from table3.
    I think this is how is supposed to be but I dont know were to place it and it doesnt work:
    Award1="Return date"-DLookup("Key","Settings","Title"=Award1)
    This is not working for me. I really need help. the supplied DB is a quick build to get what I need. then I will implement it into my working database
    Attached Files Attached Files

  2. #2
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Also forgot to post, I also asked this question in stack overflow. but no response yet.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you normalized Awards table, could link with Settings table.

    Need to change [Return date] to a Date/Time type. Change Key to a number type.

    You can calculate with expression in textbox or query.

    =[Return date]-DLookup("Key","Settings","Title='Award1'")

    But if you want to save calculated value, that requires code (macro or VBA).

    Advise not to use spaces in naming convention. Also, better not to use exactly same field name in multiple tables.

    What is link to SO question?
    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.

  4. #4
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62

  5. #5
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    If you normalized Awards table, could link with Settings table.


    Need to change [Return date] to a Date/Time type. Change Key to a number type.

    You can calculate with expression in textbox or query.

    =[Return date]-DLookup("Key","Settings","Title='Award1'")

    But if you want to save calculated value, that requires code (macro or VBA).

    Advise not to use spaces in naming convention. Also, better not to use exactly same field name in multiple tables.

    What is link to SO question?
    Would I put the DLookup into the append query or would I need to create another query just for it and if so, how would that look? Also yes I need the calculated values to be saved in those columns under the awards table. What would the code look like for that?
    Last edited by JoeJr; 09-28-2019 at 11:29 PM. Reason: Fix error

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Run an UPDATE query if you need to edit existing records with that value.

    Include expression in INSERT INTO query if creating new records.
    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.

  7. #7
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    Run an UPDATE query if you need to edit existing records with that value.


    Include expression in INSERT INTO query if creating new records.
    Okay I got it to work by adding the Dlookup in the append query by adding a new column and setting the top line as expr.[ReturnDate]-DLookup(“Key”,”Settings”,”Title=‘Award(x)Submissio ndate’”) with x being 1,2,3 since I had to do it three times. After running the append query it worked and the date values stay. I also tried creating an update query so that in the event that the value in the settings changed it would update the submission dates based on new setting for that award, but I can’t get it to work.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    UPDATE Awards SET Award1=[Return date]-DLookup("Key","Settings","Title='Award1'"), Award2=[Return date]-DLookup("Key","Settings","Title='Award2'"),Award3=[Return date]-DLookup("Key","Settings","Title='Award3'");
    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.

  9. #9
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    UPDATE Awards SET Award1=[Return date]-DLookup("Key","Settings","Title='Award1'"), Award2=[Return date]-DLookup("Key","Settings","Title='Award2'"),Award3=[Return date]-DLookup("Key","Settings","Title='Award3'");
    After creating the update Query as decribe and running it, it returns a mesg for "Enter Parameter Value" asking for input for award1. Can you please review my DB update Query to make sure I have constucted it correctly. Again, the update query is only attended to update the awardsubmission dates if the critra has changed in the settings.

    Testing2.zip

  10. #10
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    62
    Okay, I got the update to work. I was messing up the DLookup.

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

Similar Threads

  1. Replies: 2
    Last Post: 10-11-2017, 09:33 PM
  2. Add or Subtract Business Days from a Given Date
    By orange in forum Code Repository
    Replies: 2
    Last Post: 05-15-2017, 12:45 PM
  3. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  4. Replies: 4
    Last Post: 03-31-2014, 02:20 PM
  5. Replies: 1
    Last Post: 07-07-2010, 04:22 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