Results 1 to 4 of 4
  1. #1
    clippert is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    2

    Take a date from one table and retrieve the nearest future date from another table

    Hi Everyone,



    I am new to the forum and am by no means an Access expert. I'm having trouble with a Rules Database I am working on in Access 2016.
    The database I have includes two main tables: Rules and Dates. Both of these tables have date fields. I have created a form based on the Rules table. I would like to have the option to select a date from a field in the rules form and have other date fields auto-populate based on the closest future dates in the Dates table. It seems like I could put something in the criteria for the row source of the combo box. But I'm unsure about the criteria and field I should put in the query.

    Any help is much appreciated.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    in the ON CHANGE event of the date RULES date field (or ON EXIT PROPERTY)

    FutureDateField = dmin("[DATEFIELD]", "[Dates]", "[DATEFIELD] > #" & forms!formname!datefieldname & "#")

    where FUTUREDATEFIELD is the field you want filled in
    DATEFIELD is the name of the RULES date field you are calculating from
    DATES is the name of the table (DATES, incidentally I would stay away from 'dates' or 'date' for the name of fields/queries/tables since these are typically reserved words and may cause you problems)
    FORMNAME is the name of the form on which you want this calculation to happen
    DATEFIELDNAME is the name of the field containing the date you want to calculate from

  3. #3
    clippert is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    2
    Hi rpeare,

    Thanks for the reply. Unfortunately this did not work for me. The futuredatefield is titled "Submit NOT to OAH", the datefield is titled "Agency Action". I changed the name of the dates table to "OAH DATES". The form name is "RULES" (after the "RULES" table) and the datefieldname is "Submit NOT to OAH" (This field is present in both the Dates and Rules table. But I want access to choose from the list of dates in "OAH DATES" table to fill in the field in the "RULES" form).

    This is what I entered in the on change event of the datefield "Agency Action" (which is the field where one would select a date from the dropdown):
    Submit NOT to OAH = dmin("[Agency Action]", "[OAH DATES]", "[Agency Action] > #" & forms!RULES!Submit NOT to OAH & "#")

    Maybe I misunderstood your explanation? Any Suggestions?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This leads to another problem... you are using spaces in your object names which causes access problems, I would suggest you remove any spaces or special characters (#, @, %) other than the underscore from your field/object names to avoid this confusion. If an object has spaces in the name you have to encapsulate it in brackets ( [ ] )

    [Submit NOT to OAH] = dmin("[Agency Action]", "[OAH DATES]", "[Agency Action] > #" & forms!RULES![Submit NOT to OAH] & "#")

    But I doubt this will do what you want because the dmin function will always return the same value because you are using the value in the field to replace the value in the field.



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

Similar Threads

  1. Replies: 10
    Last Post: 05-15-2015, 11:35 AM
  2. Replies: 1
    Last Post: 11-16-2014, 09:10 AM
  3. Replies: 2
    Last Post: 07-17-2014, 01:46 AM
  4. Future Date that may be incremented
    By h1mself2 in forum Access
    Replies: 3
    Last Post: 12-14-2012, 04:11 PM
  5. Replies: 3
    Last Post: 11-20-2012, 01:40 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