Type Mismatch error 13 on SetValue Macro

[lmahere]

Hi guys , I am trying to create a SetValue Macro Using an expression that calculates date of birth based on an ID number. The expression works when inserted in control source but because i want to save the value in the table from the form i opted for set value which giving me the type mismatch error and its happening with all my SetValue macros . Below is my SetValue action .



Item=[Forms]![Students]![Date of Birth]
Expression = CDate(Mid([Forms]![Students]![ID Number],3,2) & "/" & Mid([Forms]![Students]![ID Number],5,2) & "/" & Left([Forms]![Students]![ID Number],2))

[John_G]

I don't use macros, so this is a bit of a guess, but Access likes date values to be delimited by # marks, so try expressing the data just as a string without CDate:

"#" & Mid([Forms]![Students]![ID Number],3,2) & "/" & Mid([Forms]![Students]![ID Number],5,2) & "/" & Left([Forms]![Students]![ID Number],2) & "#"

[Micron]

For the Imahere's information, CDate requires the string date to not only look like a date based on the user's regional settings, it must be delimited by # or double quotes (") - not single ('). As suggested, simplest is to do without the date conversion entirely, unless you need to actually change the data type so that you can do date comparisons. However, since you said it's happening to several macros, the problem probably is that you have the data type set incorrectly in the table field. CDate does more than make a date look like a date - it actually changes the data type, so the place you're keeping it has to be compatible.