Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Update Dates (Office XP)

    Hi,

    I am recording in an Access Database accidents that occur in my workplace. Part of our review process is to look at all accident reports in 3 months to ensure that any reccomendations made to avoid future accidents happen have been enacted. Currently I am manually calculating a date three months from the accident date and have written a query and report to list all the review dates for a given month.

    What i would like to be able to do is automatically add 3 months onto the accident date and put this data into my review date data field.

    Is there a simple way to do this???

    Thanks...Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Update Dates (Office XP)

    Use the DateAdd function for this.

    If you want to calculate the review date in a query, create a calculated field:
    ReviewDate: DateAdd("m",3,[AccidentDate])
    You must replace AccidentDate by the name of the field that stores the accident date.

    Note: if the accident date is August 31, the review date returned by DateAdd will be November 30, since November has only 30 days.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Update Dates (Office XP)

    Hi,

    Thanks for that, it is a great help- is there a way to do this on my data entry form as well or instead of using an update query?

    Thanks...Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Update Dates (Office XP)

    There is no need to use an update query for this. As a general rule, don't store calculated information in a table - it's superfluous. (There are some exceptions, but I don't think this is one).

    If you put the calculated field in a query, you can base your forms and reports on this query instead of on the table behind it. Then, you can have a text box on the form/report bound to the calculated field, and you can also refer to the calculated field when filtering data etc.

    If you prefer, you can calculate the review date directly on a form or report, instead of in a query: put a text box on the form/report and set its Control Source to
    =DateAdd("m",3,[AccidentDate])
    where AccidentDate must be replaced by the actual field name you use.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Update Dates (Office XP)

    Thanks for that.

    Regards...Peter

Posting Permissions

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