1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Update Query For Updating A Date Field

Discussion in 'Business Applications' started by Access07Question, Oct 31, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Access07Question

    Access07Question Thread Starter

    Joined:
    Jul 5, 2011
    Messages:
    5
    Access 2007 Update Query For Updating A Date Field

    Hi I have been asked to write a query for an Access 2007 db

    I need to update a date field in a table based on another date field in the same table

    1st date field is the person's start date (StartDate)
    2nd date field is the person's project date (ProjectDate)
    Without going into too much detail, the Project Start date always begins on the 1st of the month

    If the person's StartDate is between the 1st - 14th the project start date is counted as on the first day of the StartDate month/year

    If the person's StartDate is between the 15th - end of month the project start date is counted on the 1st day of the NEXT month

    Example:
    StartDate = 7/1/2011
    ProjectDate = 7/1/2011

    StartDate = 7/14/2011
    ProjectDate = 7/1/2011

    StartDate = 7/15/2011
    ProjectDate = 8/1/2011

    StartDate = 7/30/2011
    ProjectDate = 8/1/2011

    I would like to update the ProjectDate in an update query

    Any suggestions? Thanks
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Create a Column heading like this
    numdays: format([StartDate], "dd")
    or
    numdays: datepart("dd", [StartDate])
    this will give you the day of the month on it's own
    add a second Column
    NewProjdate: IIf([numdays]>14,Format([StartDate],"mm")+1 & "/01/" & Format([StartDate],"yyyy"),Format([StartDate],"mm") & "/01/" & Format([StartDate],"yyyy"))

    This will give you the date to use in the update query.
     
  3. Access07Question

    Access07Question Thread Starter

    Joined:
    Jul 5, 2011
    Messages:
    5
    AWSOME! Both calculations work - to use in the update query I put both statements together in the "Update To" works great - Thanks!!
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    My pleasure. (y)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1024832