Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: Working with Date and Time

    I am trying to import to MS Access XP from MS Excel XP. The particuar field I am having trouble with is a date/time field. The format that the date/time is in is:

    "2005-01-14 11:15:44 PST"
    The time zone is always set as "PST"

    The problem I am having is that when it is imported into MS Access, it is imported as Text and I cannot figure out how to change the format so the data is not deleted upon import. How can I set the format up as date/time? Any help on this matter would be greatly appreciated.

    Timmy

  2. #2
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    In the design view on your table you can set the data type to Date/Time.
    Darasen

  3. #3
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Are you using an import wizard to do this or have you created a table and are trying to paste this directly?

    If you're importing, change the type of the field that it is in the import wizard. Not sure which page it's on, but in Access 2000 I think it's the one after you define the field lengths.

    If you're pasting, alter the table's design and make it a date/time field and you should be fine.

    Also, do you need the PST at the end of the data?

    If not you could consider removing the PST in Excel.

    Hope this helps.

  4. #4
    Join Date
    Nov 2004
    Posts
    7
    I have tried the format part in Design View. I set it as

    yyyy-mm-dd hh:nn:ss ***

    but the field keeps getting deleted.

    I am using the import wizard for the import.

    As far as the PST time zone goes, I do not need it. However it would be some what time comsuming to start excel and find/replace all the instances.

    I was not sure if Access would let you format the date/time and ignore any other character in the field. Thank BTW for the speedy replys

  5. #5
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    You don;t need to replace anything try this.

    In the cell next to your date/time field insert a column if it's not balnk and try this.

    Example relies on a date/time being in cell A1 and you'll be using cell B1. You'll need to change the row and column based on your data.

    =If(right(A2, 3) = "PST", left(A2, 19), A2))

    This'll strip the PST out fast! Then just copy and paste the values elsewhere so you have your original data intact.

    Access should be able to handle the PST though, so I am surprised.

    If this doesn;t work, then give us a shout and we'll see what we can russle up!

  6. #6
    Join Date
    Nov 2004
    Posts
    7
    I'll give you a little more info so you can understand my situation. I sell product over the internet. The company I sell through lets me download reports (15, 30, 60 days worth of sales) the report is in a spreadsheet format. I sell roughly 1500-2000 items per month and am trying to figure out a timely way of importing the info into Access (hopefully not touching the spreadsheet.)

    The format the date/time when I recieve it is:
    2005-01-15 12:15:45 PST and when imported into Access, it is read as TEXT.

    Is there any way to write a format line in design view to make this a DATE/TIME field instead of just a TEXT field (so I can do queries for particular months)?

    Once again I thank you for your help!!!

    Tim

  7. #7
    Join Date
    Nov 2004
    Posts
    7
    What I will do is:

    1. open the spreadsheet
    2. find " PST" and replace all with ""
    3. import into Access and have my table format the date/time then.

    Thank you so much for your time and help. Have a good day!!!

    Tim

  8. #8
    Join Date
    Feb 2005
    Posts
    19
    Import with your wizard as normal. Copy the structure of the table you make (SourceTable) to (TargetTable) and in design change the DateTxt field to Date type. Now you have an empty table but with a proper date field. Make a query as follows: INSERT INTO TargetTable ( NewDate )
    SELECT (CDate(Left([DateTxt],19))) AS expr1
    FROM SourceTable;

    I've just tried it and it works quite well. Whether you delte and append or just append is up to you.

  9. #9
    Join Date
    Nov 2004
    Posts
    7
    Thanks, I will try your suggestion out

Posting Permissions

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