Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append query to change year in date format (2000)

    I have a database of information which contains a field for a start date and a field for an end date. The date format is entered as yyyy-mm-dd. The years are incorrect on all the dates and all should be changed to the same year of 2002. What formula would I use to change this in an append query? Also, the end date is blank. Is there a way to make an append query that will update each record and automatically make the end date the day after the start date?

    Dana

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query to change year in date format (2000)

    Hi Dana...

    I don't know if you really want an Append Query or an Update Query... but that's up to you to decide... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Either way, if the Start and End Date fields are of date/time data type, you can enter the following expressions into the query design:

    NewStartDate: "2002" & Right(CStr(Format([StartDate],"yyyy-mm-dd")),6)
    NewEndDate: CDate([NewStartDate])+1
    (substitute your own field names)

    HTH

    P.S. I just tried it myself... using an update query... and since both fields were date/time type in the table I had to change the expressions a touch... Try this...

    NewStartDate: CDate("2002" & Right(CStr(Format([StartDate],"yyyy-mm-dd")),6))
    NewEndDate: CDate("2002" & Right(CStr(Format([StartDate],"yyyy-mm-dd")),6))+1

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query to change year in date format (2000)

    Thanks for your quick response. You are right about the Update instead of the Append, and the formulas worked perfectly for what I wanted to do. I have one more question for you. Can I merge 2 tables from 2 separate databases. I made a copy of one database and entered information in and now would like to add those records to the original. How do I do that?

    Thanks again,
    Dana

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

    Re: Append query to change year in date format (2000)

    You can create a link to a table in another database (File | Get External Data | Link Tables...); you can use a linked table in queries just like you use tables that reside in the database itself.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Append query to change year in date format (2000)

    A simple append query should do what you want, although if you wanted to keep the databases separate, then Hans solution is the go.

Posting Permissions

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