vba CDate 类型不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10502802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CDate type mismatch error
提问by regulus
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?
我正在尝试使用记录集中的 CDate() 将文本字段转换为日期，但不断收到类型不匹配错误。输入文本格式为 MMDDYYYY。CDate 不能识别这种格式吗？我需要一个单独的功能吗？有任何想法吗？
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
回答by HansUp
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()如果在月、日和年部分之间没有某种类型的分隔符，则不会接受您的日期字符串。此尝试因类型不匹配错误而失败。
? 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()函数检查您的日期字符串是否采用CDate()可接受的格式。
? 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-ddformat to avoid problems with CDate().
编辑：如果系统的区域设置与日期字符串的格式不匹配，您可以将这些日期字符串转换为yyyy-mm-dd格式以避免出现CDate().
bar = "04122012" : Debug.Print CDate(Right(bar,4) & "-" & _
Left(bar,2) & "-" & Mid(bar,3,2))
4/12/2012
回答by Brian Camire
The help for CDatesays:
的帮助CDate说：
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.
CDate 根据系统的区域设置识别日期格式。如果日期、月份和年份的正确顺序不是以可识别的日期设置之一的格式提供，则可能无法确定正确的顺序。
To avoid potential confusion due to locale settings, you might use DateSerialinstead of CDate, as in expression like this (assuming Text Datealways has 8 characters in MMDDYYYY format):
为避免由于区域设置引起的潜在混淆，您可以使用DateSerial代替CDate, 就像这样的表达式（假设Text Date在 MMDDYYYY 格式中总是有 8 个字符）：
DateSerial(Right(rst![Text Date], 4), Left(rst![Text Date], 2), Mid(rst![Text Date], 3, 2))
