Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining columns (2003/11.5614.8122)

    Newbie alert! I'm trying to use the right terminology, but I just installed this morning and I'm learning the lingo.
    I've imported data from my FileAmigo db program (via a delimited txt file), using the existing columns. Now I want to combine info from three columns (Year, Month, Day) into a new (and permanent) fourth column: date. Is there a simple way of doing this? Also, Year and Day are numbers, Month is text. I'd like to have the new Date column be Date format, but for some records I only have the year -- no Month or Day -- and Date format requires more than just the year, true?

    Thanks for any help.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Combining columns (2003/11.5614.8122)

    One way to do it is:
    1. Create a new date/time type field
    2. Use an Update query to fill the new field with the date. You can use the CDate function to convert a string into a real date. It works even if you don't have the day (it assumes the 1st), but you will have to supply a month (January?) for the dates without a month. Depending on the field types, you'd use a formula something like this:
    Iif(isnull(month_field)), cdate("1 January " & year_field), CDate(day_field & " " & month_feild & " " & year_field)
    3. Check the results
    4. Delete the original day, month and year fields.
    Peter

  3. #3
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining columns (2003/11.5614.8122)

    Thanks Peter. The only problem is I don't want to create false dates. Does this sound workable: Change the null dates to February 29, and then after importing into my Timeline program, find and replace all Feb 29ths with just the correct year? If anyone has a better idea, please let me know!

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

    Re: Combining columns (2003/11.5614.8122)

    A date field *must* have a year, a month and a day. You cannot leave any of these blank. The standard procedure is to set the day to 1 if it is missing, and to set both the month and the day to 1 if both are missing, i.e. you have only a year. Just '2007' is not a valid date.

  5. #5
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining columns (2003/11.5614.8122)

    Thanks, Hans. Yes, I understand a date field must have day, month and year represented. But since I'll be exporting to a program that allows just a year, it makes sense to change my year only dates to February 29, so that later -- in the other program -- I'll only have 1/4 as many potentially "false" dates to replace, since I'm using leap-day (which occurs once every 4 years). Makes sense?

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

    Re: Combining columns (2003/11.5614.8122)

    But if you only have a year such as 2007, you cannot set the date to February 29, for February 29, 2007 would automatically be changed to March 1, 2007. A date field in Access only accepts blanks (empty values) and real, valid. existing dates. I think you're better off keeping the separate day, month and year fields, and doing the processing in the target application instead of in Access. Or, if the target application can handle it, assemble the dates into a text field in Access.

  7. #7
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining columns (2003/11.5614.8122)

    Oh, dear. I should have thought of that. Thanks.

    Well, I'd still like to go ahead with the merge Peter suggested -- with a new column, Date, set-up in "date" type, and using real, full dates where possible, and 1/1/XXXX where I don't have month and day. I've been trying to do this, but I'm having trouble setting up the update query (it's my first time at it). I've created the new Date column and started the update query, but I'm not sure what goes where.

    I put Date as the "Field:" but I'm not sure what's next. The formula Peter suggested goes in the "Update To:" field, right? And the "Criteria:" field remains blank? Do I type the formula into the field, or is there a list of expressions I can choose from, and then edit?

    Thanks for your help.

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

    Re: Combining columns (2003/11.5614.8122)

    Create a query in design view based on the table.
    Select Query | Update Query.
    Add the new Date field to the query grid.
    Leave the Criteria row blank.
    Enter the following expression in the 'Update to' row in the Date column:
    <code>
    CDate(Nz([Day],1) & " " & Nz([Month],"January") & " " & [Year])
    </code>
    If necessary, change Day, Month and Year to the actual names of the relevant fields.
    Select Query | Run or click the Run button on the toolbar.
    Access will ask for permission to update records. Confirm.

  9. #9
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining columns (2003/11.5614.8122)

    Hans,

    I still must be doing something wrong. When I enter the expression you wrote, I clicked "View" instead of "Run" and all I saw was the one column labeled date, and it was still empty. I may be getting ahead of myself. I think I need to work on some tutorials before asking more of this forum's time with such basic stuff.

    Thanks

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

    Re: Combining columns (2003/11.5614.8122)

    When you switch to datasheet view in an update query, you see the current situation, not the situation as it will be after running the update query. Currently, the date column is still empty, so what you saw was to be expected. You must select Query | Run to update the values. If you switch to datasheet view after running the update query, you should see dates in the column.

  11. #11
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining columns (2003/11.5614.8122)

    It worked! Thanks so much, Hans! !!

Posting Permissions

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