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

    Changing a field data type (2000)

    HELP!! I am trying to append a table with the same fields to another table. This table was imported/converted from and excel spreadsheet. When attempting the append query, I get an error reading saying that it did not append the specified records due to a data type conversion error. I have found the problem. When the spreadsheet was converted, the field that should be date/time was made text. I now have 3179 records with the date listed, but in a text data type. When I attempt to append, it deletes all the dates. Can I change the data type from text to date/time after the information is already in the records? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks,
    Dana

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

    Re: Changing a field data type (2000)

    Before testing anything, make a copy of your database or of your table, so that nothing is lost if things go wrong.

    You may well be able to change the data type of the field from text to date/time. If that doesn't work, create a query based on the imported table, and add a calculated column MyDate: DateValue([textField]) where TextField is the name of the text field that should have been a date. Use this calculated field in the append query instead of the text field.

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

    Re: Changing a field data type (2000)

    Hans,
    I don't think that I understand how to do the query you are talking about. What field from the table do I use? And where do I type the calculated column MyDate: DateValue([textField]) in sort or criteria?

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

    Re: Changing a field data type (2000)

    Maybe some pictures will help. Of course, I don't know the structure and field names of your tables, so you will have to translate it to your situation.

    The upper part shows part of the design grid of an append query. The "TextField" is the field in the imported table that should have been a date/time field, but in fact was imported as text; it is in the grid, but it isn't used; to the right of it is a calculated field MyDate that converts TextField to a date; it is added to the DateField field in the target table.

    The lower part shows the result of the calculated field - note that TextField is left-aligned, indicating that it is text, and that MyDate is right-aligned, indicating that it is a real date (I have - as date separator).
    Attached Images Attached Images
    • File Type: png x.PNG (3.9 KB, 0 views)

Posting Permissions

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