Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query Help (Access 97)

    Not so good with Access and I am trying update two joined tables based on information from another joined table. The situation is as follows:

    If an Employer ([Compid]key)has not had a [contactdate] (many join) for 2 years from current date, then [status]= "INACTIVE", [InactiveDate] = Date(), [ClosureReason] = "No Activity for 2 years"

    Update query currently looks like this:

    UPDATE (tbl_Employer INNER JOIN tbl_EmployerServices ON tbl_Employer.COMPID = tbl_EmployerServices.COMPID) INNER JOIN tbl_EmployerStatus ON tbl_Employer.COMPID = tbl_EmployerStatus.COMPID SET tbl_EmployerStatus.InactiveDate = Date(), tbl_EmployerStatus.ClosureReason = "No Activity for 2 years", tbl_EmployerStatus.ClosureDate = Date(), tbl_Employer.Status = "INACTIVE"
    WHERE (((tbl_EmployerServices.ContactDate)=DateAdd("yy", 2,[contactdate])));

    Thanks for any help!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Update Query Help (Access 97)

    Are you getting any error messages when you try to execute the query, or does it just not do what you want it to?

    One possibility that might be causing it to not do any updating is the WHERE clause.
    <font color=blue>
    WHERE (((tbl_EmployerServices.ContactDate)=DateAdd("yy", 2,[contactdate])))</font color=blue>

    will only do an update if the two dates are exactly equal, including the time. In general these kind of queries would use a greater than or equal to condition rather than a strict equality.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query Help (Access 97)

    Acutally it updates all records that have a Service in the table. I just need it to update the record whos services is older that 2 years. I also have combined Employer and status into 1 table, hoping it would help.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Update Query Help (Access 97)

    What is [contactdate] in your WHERE clause? If it is xxxx.ContactDate, then the WHERE clause should prevent any records from being updated, as the two dates should never be the same. A possibility is the "yy" you are using to indicate the Interval Type in the DateAdd function. In Access 2000, to add Years, you use "yyyy". I think Access 97 is the same, so try changing that part of the DataAdd function and see if it works.
    Wendell

Posting Permissions

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