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 ```

Accepted Answer

`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 
```

CDate type mismatch error

Solution

Problem