Convert text to date

[Leo_Polla_Psemata]

Just curious, did you try my suggestion?

Hi, yes i did, however, the "text" that holds the date is 211130 , yymmdd, by using CDate(Format([TextField], "00-00-00")) , i cannot convince the system which two digits relate to yy. I don't want to change my whole system settings as this might affect other sections, anyway, by using the string functions, i twisted the text and now it is just fine.

 

[Leo_Polla_Psemata]

Public Function CstrDate(strDate As String) As Date
  CstrDate = DateSerial(20 & Mid(strDate, 1, 2), Mid(strDate, 3, 2), Mid(strDate, 5, 2))
End Function

Hi Majp
Just to give some more info, i download tables from our business system, then i process and automate several tasks in my daily activities at the office. I need to use this in a query field so the above function, is way to advance for a casual user as I am.

I made the below and works.

SO1: Format(DLookUp("(Mid([txt],95,2))&'/'&(Mid([txt],93,2))&'/'&(Mid([txt],91,2))","txt1","[bl]='" & [bl] & "' and [Lin1]='12' and [ot]='1'"),"dd/mmm/yyyy")

Anyway, many thanks for your time and input.

 

[theDBguy]

Hi, yes i did, however, the "text" that holds the date is 211130 , yymmdd, by using CDate(Format([TextField], "00-00-00")) , i cannot convince the system which two digits relate to yy. I don't want to change my whole system settings as this might affect other sections, anyway, by using the string functions, i twisted the text and now it is just fine.

Hi. Thanks for the additional information. What did you expect the result to be? This is what I got.

If you're saying you got a different result because you have a different Regional Settings, then I guess that would make sense. Cheers!

 

[Pat Hartman]

@theDBguy ,
The problem is that Access is making assumptions based on the values. If mmddyy is rational, that is the assumption. Look at the last one, the only rational interpretation is ddmmyy due to the individual values. but the one before that could be ddmmyy also but Access assumes yymmdd
print cdate(format("101130","00-00-00"))
10/11/2030
print cdate(format("101112","00-00-00"))
10/11/2012
print cdate(format("101022","00-00-00"))
10/10/2022
print cdate(format("221022","00-00-00"))
10/22/2022
print cdate(format("221021","00-00-00"))
10/21/2022
print cdate(format("221033","00-00-00"))
10/22/2033