CDate类型不匹配错误

问题描述

我正在尝试使用记录集中的CDate()将文本字段转换为日期，但始终收到类型不匹配错误.输入的文本格式为MMDDYYYY. CDate无法识别这种格式吗?我需要一个单独的功能吗?有什么想法吗?

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

推荐答案

CDate()在月，日和年部分之间没有某种类型的分隔符的情况下将不接受您的日期字符串.尝试失败，并出现类型不匹配错误.

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")

如果有帮助，可以使用IsDate()函数检查日期字符串是否采用CDate()格式.

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

编辑:如果系统的语言环境设置与日期字符串的格式不匹配，则可以将这些日期字符串转换为 yyyy-mm-dd 格式以避免CDate()出现问题.

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类型不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！