changing data types (97)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

oh man, Steve, you don

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

Your update query should select the NewField, and have as the “update to” field the expression:
CDate(oldfield)

Reply | Quote