CDate type mismatch error

Question

I'm trying to convert a text field into a date using CDate() in a recordset but keep getting a type mismatch error. The input text format is MMDDYYYY. Does CDate not recognize this format? Do I need a separate function? Any ideas?

Text Date  -> Converted Date
---------     --------------
04122012   -> 04/12/2012


Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_dates", Type:=dbOpenDynaset)

Do Until rst.EOF
rst.Edit
rst![Converted Date]=CDate(rst![Text Date])
rst.Update
rst.MoveNext

Loop

Set rst = Nothing
Set db = Nothing

Answer 1

CDate() won't accept your date string without some type of delimiter between the month, day, and year parts. This attempt fails with a type mismatch error.

? CDate("04122012")

If it's helpful, you can use the IsDate() function to check whether your date strings are in a format CDate() will accept.

? IsDate("04122012")
False
? IsDate("04-12-2012")
True
? IsDate("04/12/2012")
True
? CDate("04-12-2012")
4/12/2012
bar = "04122012" : Debug.Print CDate(Left(bar,2) & "-" & _
    Mid(bar,3,2) & "-" & Right(bar,4))
4/12/2012 

Edit: If there is a mismatch between your system's locale setting and the format of your date strings, you can transform those date strings to yyyy-mm-dd format to avoid problems with CDate().

bar = "04122012" : Debug.Print CDate(Right(bar,4) & "-" & _
    Left(bar,2) & "-" & Mid(bar,3,2))
4/12/2012 

Answer 2

The help for CDate says:

CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings.

To avoid potential confusion due to locale settings, you might use DateSerial instead of CDate, as in expression like this (assuming Text Date always has 8 characters in MMDDYYYY format):

DateSerial(Right(rst![Text Date], 4), Left(rst![Text Date], 2), Mid(rst![Text Date], 3, 2))