Results 1 to 4 of 4
  1. #1
    rb26dett26 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    8

    Constraints for Date Column

    I recently have made a Lifeguard Employee Management Database and I am in the process of changing some small things for the better.

    I wanted to add a constraint to the Date Column for example.

    current date is November 29th, 2016



    If (the current date) is approaching the expiration date of the employee by 4 months,
    Change employee date Cell to RED


    Lifeguard certifications expire after two years. I have their expiration dates in the "lifeguard Expiration Date" column and would like to have a better visual cue when they will expire.

    It has been an easy journey to make the database but the code behind access is what gets me.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you would put this logic in a query, to get the #months remaining....
    select *, DateDiff("m",[expDate],Date()) as MoRemain from table.

    this field will show the #months remaining. Then in the form, put login to colorize the text box.
    Code:
    sub form_onCurrent()
     if txtExpMo < 5 then
          txtBox.backcolor = vbRed
     else
          txtBox.backcolor = vbWhite
     end if
    end sub

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Take a look at Conditional Format for the control
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Whether you can do this in VBA code or using Conditional Formatting really depends on the type of Form you're talking about. Code can only be used for this if you're talking about a Single View Form, while Conditional Formatting is a must for Datasheet and Continuous View Forms...and can be used for Single View Forms, as well.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  2. Replies: 23
    Last Post: 04-08-2015, 12:38 PM
  3. Need to use constraints
    By tsvetkovdimitar in forum Access
    Replies: 4
    Last Post: 11-19-2013, 01:39 PM
  4. Query with multiple date range constraints.
    By younggunnaz69 in forum Queries
    Replies: 2
    Last Post: 12-26-2011, 10:45 AM
  5. Replies: 1
    Last Post: 04-15-2010, 02: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