Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2016
    Posts
    2

    Unanswered: Ms Access 2007 Due Date Pop up Tutorial

    yo...

    im a student in intern programe right now. my organization give me a ms access file that contain information of contract workers. they ask me to use access and make a pop up to tell the user 3 months before contract expired. what make im more crazy when she ask me to make due date field become bling bling or shiny to make us easy to check. im not familiar with access and i never use it before. what i was google before and i dont get the answer that i want.

    i already post the same question on other web and I hope I got what I want.

    please i need help i dont care who you are and i really want to say terima kasih (thankyou)

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    if dateDiff("m",Date(),[ExpireDate]) = 3 then msgbox "Contract expires in 3 months"""

  3. #3
    Join Date
    Jun 2016
    Posts
    2
    thankyou for your reply..
    im not familiar using access. can you teach me step by step how can I use that and what the meaning by each of the term?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are an intern so presumably part of that process is to learn something
    have a look at google and see if you can fidn out what datediff is, what it does what its limits are

    having done that you can apply whatever logic you need.
    popups are in my experience fine for very occasional / serious feedback or where there is a need to complete a certain step before another action is taken. Outside of that I hate 'em, especially of 'become bling bling or shiny '

    what I think your employer is requesting is set the background colour of (I quess) the expiry date if its less than 3 months
    What I'd do is write a function that takes a date and compares it to the current date and returns an appropriate value
    the reason for putting this in a function is that youi may need to call it from more than one place

    as a first cut, id probably write it with a simple return value of true or false
    Code:
    private function DateRangeCheck(thisDate as date, optional thatDate as date, period as string, interval as integer) as boolean
    'function checks to see if a date is within interval periods of a date
    'there are optional parametes (in case you want to re use this code elsewhere or use different values / settigns)
    'thisDate is required, it should be the date you want to see is within a specific range of the (optional) thatDate
    'if thatDate is not supplied use the current date
    'if period isn't supplied assume 'M' for months
    'if intervals isn't supplied assume 3
    'it returns a true false value (true if the supplied date is within n periods, false if not)
    DateRangeCheck = false 'set or default return value
    'basic validations
    if isnull(thisDate) then 'the user is a comedian, so return false
      exit function
    endif
    if isnull(thatDate) then use the current system date
      thatDate = date() 
    endif
    'interval must be positive in range 1......n, you decide what the upper limit should be on range checking. Ive used 12 as an arbitary limit
    if isnull(interval) or (interval <= 0 or interval >12)  then 'crap data but we override or set defaults
      interval = 3
    endif
    'ok now check the value of period, we coudl make this easier on ourself by declaraing an enumeration (look up enumerations)
    if period = "m" or period = "ww" or period = "d" then 'do nothign as we can aaccept any of these
      'see: https://support.office.com/en-us/article/DateDiff-Function-e6dd7ee6-3d01-4531-905c-e24fc238f85f for valid parameters in the datediff functuion
    else
      period = "m" 'set a default of m:months
    endif
    'OK so we now think we have sane parameters
    'so do the calculation. Id probably use dateadd (or datesub) but datediff gets tot he same answer
    if datediff(period, thatDate, thisDate) >= interval then 
      DateRangeCheck = false
    else
      DateRangeCheck = true
    endif
    end function
    bear in mind the bove code is untested, will need debugging and will need a willing ness to leran and understand what is going on.
    you may need to reverse the order of the terms in the datediff

    you then call it using any one of the following
    DateRangeCheck(ExpireDate) 'basic implementation assumes defaults
    DateRangeCheck(ExpireDate,cdate("31/12/" & year(date()) 'uses the end of the current year as the upper limit
    DateRangeCheck(ExpireDate,,"ww",3) 'compare is date within 2 weeks of now

    you could alter the return value to indicate if the supplied date is within the range, not reached the range or beyond the range

    OK so thats the core function done
    next you need another function which calls that function to do something
    Code:
    private function setExpiryDateBackground(ExpiryDate as date)
    if DateRangeCheck(ExpiryDate,date(),"m",3) = vbtrue then
      expirydate.backcolor = vbRed 'this needs to be the name of the control containing the expirydate
      'or
      messagebox "oi &*()&)(* you are within 3 months of your contract expiring" ' or whatever you want to display as a message box
    else
      expirydate.backcolor = vbWhite 'you will be better off using the background color of a control that doesn't change
    endif
    end sub
    then in the forms various events test to see what the background should be. at the very least that will be
    the forms onCurrent event
    the Expirydates afterupdate event

    the rangecheck function should probably be in a common code module
    the form specific function should be in the relevant forms code module
    ..the reason
    well the form specific function changes things only in that form
    the other could be in a public module if say you wanted to to use the same business logic in more than one form or report

    as to how to extend it well you could return an integer
    + means still not within range (ie expiry date is more than the specified periods in future)
    0 means in range (ie expiry date is within the specified periods in future)
    - mean s out of range 'beyond' expiry (ie expiry date has already elapsed)
    ... why would I do that well that would allow to set say 3 states for the forms expiryevent control

    Code:
    private function setExpiryDateBackground(ExpiryDate)
    select case DateRangeCheck
      case is 0: 'warning, expiry date is within range
        expirydate.backcolor = vbYellow 'this needs to be the name of the control containing the expirydate
      case is < 0: 'alert, has already expired
        expirydate.backcolor = vbRed 'this needs to be the name of the control containing the expirydate
      case else 'no peoroblems expiry date is still far wnough out not to worry
        expirydate.backcolor = vbWhite 'you will be better off using the background color of a control that doesn't change
    endif
    end sub
    Last edited by healdem; 06-24-16 at 11:34.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •