changing from text to date

[iris b]

I inherited a HUGE append table with an error in it and I'm not sure how to fix it. The person labeled the "week" column as text, NOT as a date. I tried going into the table to switch the property, but it says that the file is too large to convert like that.

Is there a way of changing the data type from text to week WITHOUT making a whole new one? I'm a new user, so as basic an answer as possible would be much appreciated

The problem that I am dealing with is that I'm trying to track the last 52 weeks of data, but keep getting data from 2002 instead (the dates are listed as: "12.29.03", for example)

Thank yoU
*iris

 

[John Nurick]

Hi Iris,

Open the table in Design view and add a date field, then save the table.

Next, create an update query that updates the new field to
CDate([Week])

Check that this has worked right (did it get the day and the month
confused?). If it did, you can then delete the text field.

If not, try again, using something like
DateSerial(Right([Week],2), Left([Week],2), Mid([Week],4,2))
- though even this will need fine-tuning depending on whether you have
leading zeros (e.g. 1.2.03 vs 01.02.03).