Convert Text Column To Date

[jo15765]

I have a text column in a table that should actually be a date. Well I can't go back and change the table so how can I format a text column to display as a date mmddyyyy in an access query?

[JoeM]

Do you just need to change the way it looks (the format), or do you actually need it to be a date field so you can perform calculations on it?
What does the data look like right now in the Text field?

[Rawb]

As long as the text is already formatted as a date, you can use CDate() to convert it in realtime. Otherwise, you'll need to massage the output to look like a date before using the function.

Show us a couple of examples if using CDate() doesn't work and we'll see what we can do

[June7]

Why can't you fix the table?

[jo15765]

Do you just need to change the way it looks (the format), or do you actually need it to be a date field so you can perform calculations on it?
What does the data look like right now in the Text field?

Yes, I just need to change the look. It shows in the table as 1162015 I need it to show as 1/16/2015.

[June7]

Consistency is critical in manipulating strings. Months and days can be 1 or 2 digits. This would be fairly easy if the text were like: 01162015, 11052015, 04072015 (note the placeholder zeros). How should we know that 1112014 is 1/11/2014 or 11/1/2014? 1222014 is 1/22/2014 or 12/2/2014? I don't know how many more there could be.

Need to fix the data then fix the table.

Fixing the data, if there is a lot, will likely be a long, tedious effort. Any strings less than 8 characters will have to be adjusted with placeholder zeros. Could assume the 6-character strings should have two zeros and a calculation could deal with that. It's the 7-character values that get tricky. For some the mon/day will be obvious. For instance, 1312014 cannot be 13/1/2014, 9202014 cannot be 92/0/2014. Again, another calc can identify these invalid months/days and construct a correct date value.

Fix the 6-character values first. Then do another pass to calc and fix some of the 7-character values. What's left will probably need case-by-case review and manual edit.

[Rawb]

June7 is absolutely right.

Unless the month is the ONLY part that can be a single digit, I think you're out of luck.