I have a form that pulls data from a sheet and presents it in a certain way allowing them to edit the data that goes back in the sheet - however VBA was presenting the dates as numerical, so I put CDATE around the function and this worked, for some. However for one of them I am getting the Run Time Error 13 - Type Mismatch error when the data that I am trying to CDATE is blank.

95% of the contents is numerical dates, however some are text - how do i run either an if or an iferror on the CDATE to allow the below to work:

Me.txtDestrDate.Value = CDate(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 11))

Sometimes the contents may be blank, a numerical date, "active", or "n/a" - i.e blank, date, or text - but all pull through that link, so it is just the formatting that needs to change based on the contents.

Initially i tried just putting an iferror in front of the CDATE, as i would in excel, though that did not work.

Please could someone help

CodePudding user response：

Try using CVDate that accepts Null values:

Me.txtDestrDate.Value = CVDate(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 11))

CodePudding user response：

As per the Type Conversion doc, you need to confirm the value can be converted to a date before calling the CDate() function:

Use the IsDate() function to determine if date can be converted to a date or time.

If IsDate(value) Then txtDestrDate.Value = CDate(value)

Gustav's solution above is simple and probably fits your needs, just to add that the CVDate() function returns a Variant instead of an actual Date type.

So, I suspect if the value is null, the function will return null (keep in mind if the value is processed further to avoid errors).

A CVDate function is also provided for compatibility with previous versions of Visual Basic. The syntax of the CVDate function is identical to the CDate function; however, CVDate returns a Variant whose subtype is Date instead of an actual Date type. Since there is now an intrinsic Date type, there is no further need for CVDate.