Convert string to date

Deon Olivier 101 Reputation points
2022-01-26T07:14:26.84+00:00

Hi all,
Using MS Access 2016.

I have a string called "FinalStr". The value of the string is "0001315454022" - the first 6 digits is the birthdate (2000/01/31), but the year is not captured as "2000", but only the last 2 digits of the year. The next 4 digits is a control number and the last 3 digits, is a serial number.

I use:
MyStr = CDate(Left(FinalStr, 2) & "-" & Mid(FinalStr, 2, 2) & "-" & Right(FinalStr, 2))

This line I expect to result in "2000-01-31", but it does not. The result is an error 13 (Type Mismatch), or it results in "00:00:00".
I could figure out that the error occur in the first part (MyStr = CDate(Left(FinalStr, 2) ), but the subsequent code also does not yield what I expect.

Any suggestions?

Thanks
Deon

0 comments
Accepted answer
  1. Viorel 112.4K Reputation points
    2022-01-26T14:05:36.167+00:00

    You should decide how to interpret the two digits of the year. If your dates are always in the past, then consider this approach: 

    MyDate = DateSerial(IIf(CInt(Left(FinalStr, 2)) <= Year(Date) Mod 100, 2000, 1900) + Left(FinalStr, 2), Mid(FinalStr, 3, 2), Mid(FinalStr, 5, 2))
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Doug Robbins - MVP 716 Reputation points
    2022-01-26T08:00:38.21+00:00

    Use

    MsgBox "Birthdate: 20" & Left(str, 2) & "-" & Mid(str, 3, 2) & "-" & Mid(str, 5, 2)

    0 comments
  2. Viorel 112.4K Reputation points
    2022-01-26T08:13:04.047+00:00

    Try this too: 

    Dim MyDate As Date
MyDate = DateSerial(2000 + Left(FinalStr, 2), Mid(FinalStr, 3, 2), Mid(FinalStr, 5, 2))
    0 comments
  3. Deon Olivier 101 Reputation points
    2022-01-26T10:11:27.543+00:00

    Hi Viorel,
    Your suggestion works, but it now returns "2097-01-31" if the birthdate is "1997-01-31".
    I assume it is because of the DateSerial being 2000. How will I distinguish between birth year 1900-1999 and 2000-2999?

    I am coding as follows:

    Dim MyStr as Date

    tempidholder = Format(Str(IDNumber), "0000000000000") 'This is to convert the number to string
    .
    .
    .
    If Left(tempidholder, 1) = "0" Then
    FinalStr = Left(tempidholder, 6) 'Stores just the first 6 digits
    MyStr = DateSerial(2000 + Left(FinalStr, 2), Mid(FinalStr, 3, 2), Mid(FinalStr, 5, 2))
    BirthDate = MyStr 'If first 6 digits is "000131", returns "2000-01-31". If first 6 digits is "04-01-31" returns "2004-01-31" - all correct
    Else
    FinalStr = Left(tempidholder, 6)
    MyStr = DateSerial(2000 + Left(FinalStr, 2), Mid(FinalStr, 3, 2), Mid(FinalStr, 5, 2))
    BirthDate = MyStr 'If first 6 digits is "97-01-31", returns "2097-01-31" in stead of "1997-01-31"
    End If

    If I change the line "MyStr = DateSerial(2000 + Left(FinalStr, 2), Mid(FinalStr, 3, 2), Mid(FinalStr, 5, 2))" to read "MyStr = DateSerial(1900 + Left(FinalStr, 2), Mid(FinalStr, 3, 2), Mid(FinalStr, 5, 2))", I also get the wrong return.

    .
    .
    .
    Else
    FinalStr = Left(tempidholder, 6)
    MyStr = DateSerial(1900 + Left(FinalStr, 2), Mid(FinalStr, 3, 2), Mid(FinalStr, 5, 2))
    BirthDate = MyStr 'If first 6 digits is "01-01-31", returns "1901-01-31" in stead of "2001-01-31"
    End If

    Thanks
    Deon

    0 comments
  4. Doug Robbins - MVP 716 Reputation points
    2022-01-26T13:27:09.193+00:00

    The following will display a date from last century if the year is greater than the present year

    If Left(FinalStr,2)< Format(Date, "yy") then
    MsgBox "Birthdate: 20" & Left(FinalStr, 2) & "-" & Mid(FInalStr, 3, 2) & "-" & Mid(FinalStr, 5, 2)
    Else
    MsgBox "Birthdate: 19" & Left(FinalStr, 2) & "-" & Mid(FInalStr, 3, 2) & "-" & Mid(FinalStr, 5, 2)
    End If

    0 comments