Solved Convert a text string date to dd/mm/yyyy

[Number11]

Hwllo, so i am trying to confirm a date string and format as date

20231129 would be 29/11/2023

what's the best way to confirm via query

 

[tvanstiphout]

If you add 2 dashes, you have ISO date pattern, and Access can handle it:
? IsDate("2023-11-29")
True

 

[theDBguy]

Depending on your situation, you might also be able to do it the other way.

?Format(#29/11/2023#, "yyyymmdd")="20231129"

(untested)
Just a thought...

 

[Minty]

As Tom says just add some - to your string and access will accept it as a date

RealDate:  DateValue(Left(MyString,4) & "-" & Mid(MyString,5,2) & "-" &  Right(MyString,2))

 

[Number11]

Thanks all i got it working using

RealDate: DateSerial(Left([T_Date], 4), Mid([T_Date], 5, 2), Right([T_Date], 2))

20231130 = 30/11/2023

 

[ebs17]

CDate(Format("20231129", "@@@@-@@-@@"))