Results 1 to 5 of 5

Thread: Due Date Flag

  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Due Date Flag

    I've created a Corrective Action Database for our Quality department.
    For each Corrective Action that's created a report can be generated ,which can be sent to the person responsible for responding to the corrective action before a specific due date.
    I need have the ability to be flagged when the due date has come to term.
    Is there, in Access, a way to be flagged or notified when a date has come due?

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Yes,
    However, you have to trigger the database to go calculate the dates.
    1) One possibility... on open you could run a function that compares the dates from some field in some table and compare them to now(). And if the DateDiff is bigger than what you wanted you can programtically send an email.
    2) Perhaps an easier method is to run a report. One of the fields on the report is a calculated field that has conditional formating. Show it in red text if the date for that row is overdue.
    There are many options.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The key point jpshay is stating is that you have a table with a field in the database to "store" the date value and you compare this with the current date (and/or time) which will then trigger whatever event you want it to (ie. print a report) once the value in the field is greater than today's date/time. Then your code updates that date value in the table when appropriate.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2009
    Posts
    2
    Thank you for your help and suggestions.
    I would like to extend that help by asking if you could give an example of code which would accomplish what you suggest.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    That's a little difficult to do without knowing your table design but you can try this little example:

    1. Create a table called tblLookupDateValue. Create a field in that table called MyDueDate (date/time field type.)
    2. Open the table and create 1 record by entering a date in the MyDueDate field such as 09/20/2009.
    3. On a form, create a button called cmdCheckDueDate. In the [Event Procedure] for the OnClick event of that button, add this code:
    Dim LDate as Date
    LDate = dlookup("[MyDueDate]","tblLookupDateValue")
    dim NDate as Date
    NDate = Date()
    if LDate > NDate then
    msgbox "The date value in the table is greater than today's date"
    else
    msgbox "Today's date is greater than or equal to the date value in the table."
    end if

    experiment with changing the record's date value in the table (but DON'T add a 2nd record in the table, edit the 1st record's date value) and then opening the form and clicking the button.

    After testing, you can then move the code above to another event such as in the Onload or OnOpen event of a form (such as your main menu form.)
    Last edited by pkstormy; 09-19-09 at 15:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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