Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: Importing Date field

    I have imported the date (yyyy/mm/dd) into Access as a text file, how do I go about in changing this to a date field so it recognizes this date? I'm just a beginner with Access.

  2. #2
    Join Date
    Aug 2004
    Location
    Abu-Dhabi, U.A.E
    Posts
    5
    CDate() converts any valid date in text format, to a Date Field.

  3. #3
    Join Date
    Aug 2004
    Posts
    13

    Red face I could use some help with this date conversion too please!

    Hi,
    I've been using access for a while but have recently been given a database to try and fix.
    It's the same problem as q_ube in that there's a field called RenewalDate that is in fact just a text field.

    I tried changing the form design on this field but obviously that doesn't actually reformat the existing data.
    Do I need to create a modify query to use this?

    If you could help with an example of this CDate() usage it'd be much appreciated.

    Table name is QDATA and field trying to change is RenewalDate

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    in table design mode, change text field type to date/time, access will convert data for you. have a backup before doing that though. wrong date values would be discarded in the translation.
    ghozy.

  5. #5
    Join Date
    Aug 2004
    Posts
    13

    Talking

    Cheers for that.
    I changed it but still couldn't query properly using the Date() function so I exported all the data to a text file, backed up the main table then copied the main again just taking the structure.

    I then changed the field type to Date/Time and set the input type 00/00/0000 and when I reimported the data into this table, bosh!

    Worked for me so if anyone else tries it remember to MAKE A BACKUP FIRST!

    Thanks again!

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    use @ not like 0's
    Ryan
    My Blog

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    If you are "reformating" text dates to real dates using the "access smarts" is not the smart thing to do....

    This usually works in ISO format (YYYY/MM/DD) but sometimes fials/errors.

    If you use dateserial(Year,Month,Day) and using left, right and mid to extract the data from the string you will be sure there are no errors....

    Regardz

Posting Permissions

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