Convert string to Date

[dsatino]

I'm getting a tab delimited file that I need to import into an access DB. The date field comes over as an 8 character string in the yyyymmdd format.

Does anyone know an easy way to convert this to a date or a pre-existing function?

[TheSmileyCoder]

Try the built in function cdate. It should meet your needs

[dsatino]

You might think so, but it doesn't convert this to a date. As a string, I get 'expected expression'. As a number I get 'out of range'.

I expected the number to fail, but I thought the string would work.

[Rabbit]

That's because the yyyymmdd format isn't a recognized format by Access. Strings without delimiters aren't recognized as dates.
CDate(Mid(strDate, 5,2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4))

[dsatino]

Yeah, I had a feeling this was the route I was going. I was just figuring there was a predefined function. Oh well, I guess I'll just write it. :)

[NeoPa]

You can happily use this that I produced a while back :

'YMDToDate takes varDate in YYYYMM or YYYYMMDD format and returns it as a date.
'28/01/2004 Return Null if not valid format.
Public Function YMDToDate(varDate As Variant) As Variant
    Dim strDMY As String
 
    strDMY = Mid(varDate, 5, 2) & "/" & Left(varDate, 4)
    If Len(varDate) = 8 Then strDMY = Mid(varDate, 7, 2) & "/" & strDMY
    YMDToDate = Null
    If IsDate(strDMY) Then YMDToDate = CDate(strDMY)
End Function