Text type date change to date type date

[Chrissy]

Presently, my credit card table stores expiration date as text type, (10/11
for Oct 2011). I need to convert it to a date type field so I can test on
date type. Once it is a date type I can format for my use.

I don't know how to do this conversion. Can someone please advise?
Remember, it is the table I want to change.

Thanks.

 

[Douglas J. Steele]

Oct2011 isn't a date: you need a day as well.

Since you don't currently have dates in the field, you cannot convert the
existing field to a date.

You'll have to add a new date field then run an Update query that converts
the text string to a date. Assuming you have 2 digits for month (i.e. Jan,
2011 is 01/11), you should be able to use the following to create a date
that's the first day of the month:

DateSerial(2000 + CLng(Mid(TextField, 4)), CLng(Left(TextField, 2)), 1)

If you want the last day of the month, use

DateSerial(2000 + CLng(Mid(TextField, 4)), CLng(Left(TextField, 2)) + 1, 0)

If you don't have 2 digit months everywhere, you'll need to use

DateSerial(2000 + CLng(Mid(TextField, InStr(TextField, "/") + 1)), _
CLng(Left(TextField, InStr(TextField, "/") -1)) 1)

or

DateSerial(2000 + CLng(Mid(TextField, InStr(TextField, "/") + 1)), _
CLng(Left(TextField, InStr(TextField, "/") -1)) + 1, 0)

 

[Chrissy]

Thank you Douglas! That's gonna work.