extract date from string (1 Viewer)

[laffeg]

hi
got a field that is a long string of information with spaces to separate different bits of information that include a date in simple ddmmyy no slashes or dots
I'm using various left and instr functions to separate out the information into individual fields in a query
all working other than I can't get the date to show as a date

Expr5: Format$(Left([expr4],InStr([expr4],"-")-2),"dd/mm/yy")

example
161220 I want to have as date value 16/12/20 but instead I am getting 27/05/41 ??

what am I missing ?
thanks in advance for any help

 

[Minty]

Check out the date serial function - something like

DateSerial(Mid([YourField]5,2),Mid([YourField],3,2),Left([YourField],2))

DateSerial Function - Microsoft Support

support.microsoft.com

 

[laffeg]

excellent - gold star to you !
works a treat
many thanks

 

[Pat Hartman]

To clarify the mystery of your conversion issue - dates are stored internally as double precision numbers. The integer part is the number of days since Dec 30, 1899. The decimal part is the fraction of the day since midnight.

So 161220 is probably 27/05/41

 

[Gasman]

? cdate(161220)
27/05/2341

 

[Pat Hartman]

Thanks for the correction
I didn't count the digits
? cdate(16122)
2/20/1944
I was just guessing that 16000 was in the 40's. I didn't have Access open at the time.

 

[Gasman]

Not a correction Pat, just showing it was not even 2041

 

[Pat Hartman]

Well, Access thought it was 1941 and who are we to argue with that