Results 1 to 5 of 5
  1. #1
    timPM is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    10

    Restrict a date field to a date range based on a relationship


    Hi!

    So I'm gonna be creating an enrollment form where students would sign up for classes. I have an enrollment date field that will say when they enrolled in the course. I also have a field in the enrollment table called sec_ID which is linked to a section table that defines the start and end date for the course in individual fields "sec_start_Date" and "sec_end_Date".

    Is there a way to restrict what is typed into the enrollment date field based on the section that the class is (start/end date)

    Thank you!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Combo Boxes are pretty popular for controlling what data is used to update tables. On a form like that, the only fields that should be getting updated should be Foreign Key fields. Populate the FK fields with data from another table's Primary Key fields.

  3. #3
    timPM is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    10
    I don't think a combo box is what I'm looking for. I basically just want a date field to check to see if the date entered is between two dates that are in another table

    I've tried using the validation expression - [e_Date] BETWEEN [sec_start_Date] and [sec_end_Date] .... however it says it can't find [sec_start_Date] because it's in another table. There are relationships between the tables however that link through foreign keys.

  4. #4
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    rather than using the validation rule (which is applied at the table level) I would use some code in the datecontrol afterupdate event

    something like

    if not [e_Date] between dlookup("sec_Start_Date","anothertable","some criteria") AND dlookup("sec_end_Date","anothertable","some criteria") then
    handle what you want to do here
    end if

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You could create an expression using two Doamin Lookups like Ajax suggested. However, there may be a better way to go about it. If you like, upload you DB here. Name the form that you are working on and I will take a look at it. I am curious about the table structure and how the magic date is being selected, as well as what you are doing once you discover the date range. It seems odd to me that your date range is coming from two tables.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-22-2014, 06:11 PM
  2. Replies: 3
    Last Post: 09-11-2013, 09:49 AM
  3. Message Box Based on Date Range
    By MSAccessOldTimer in forum Programming
    Replies: 4
    Last Post: 05-14-2012, 04:25 PM
  4. SUM based on date range in query builder
    By tjstromquist in forum Queries
    Replies: 1
    Last Post: 04-04-2012, 09:07 AM
  5. joins based on date range
    By broecher in forum Database Design
    Replies: 4
    Last Post: 11-04-2011, 06:32 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