Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    changing data types (97)

    changing data types
    if you change a field from date to text & then find later on that you need to change again to date, what to do?

    I have some fields that look like 100501 in text. I need it as a short date 10/05/01. I tried just changing the types in a testing area, but ALL my text fields wound up deleted. How to avoid this?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: changing data types (97)

    The best thing to do in such a situation is to create a new field altogether and then use an update query to move the data from the old field into the new field. If everything checks out okay, then you can delete the old field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing data types (97)

    Mark,
    My text values in the old field did not update to the new table at all. A type converstion failure happened. The new field is a date type, and the old field has text in it, did I miss a step?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing data types (97)

    Try using the Format function,
    e.g. Format([oldfield],"dd/mm/yyyy") to update the new field which is set to a Date/Time

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing data types (97)

    oh man, Steve, you don

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing data types (97)

    You could try first splitting your [oldfield]
    then formatting as date
    e.g.
    Format(left([oldfield],2 & "/" & mid([oldfield],3,2) & "/" mid([oldfield],5,2), "mm/dd/yy")
    - I reversed the day/month as your data looks American format

    You could also put a check in that the length of the oldfield is 6 characters

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: changing data types (97)

    Your update query should select the NewField, and have as the "update to" field the expression:
    CDate(oldfield)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing data types (97)

    how about this updateTo criteria for the old field in an update query, it seems to be giving me exactly what I want, not too shabby.

    Left([NewMain]![DateSentTo],2) & "/" & Mid([NewMain]![DateSentTo],3,2) & "/" & Right([NewMain]![DateSentTo],2)

Posting Permissions

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