Format Date Help Needed

[Sam]

I have a field with dates formated as text mmddyy
Examples
120149 should be 12/01/2049
030130 should be 03/01/2030
021796 should be 01/17/1996
061797 should be 06/17/1997

I've tried a couple of thing but I keep getting 1949 & 1930 for the
ones that should be 2049 & 2030.
Could someone help me with a formula to convert the dates to the MM/DD/
YYY format with the proper year.

Thanks
Samantha

 

[Dirk Goldgar]

I have a field with dates formated as text mmddyy
Examples
120149 should be 12/01/2049
030130 should be 03/01/2030
021796 should be 01/17/1996
061797 should be 06/17/1997

I've tried a couple of thing but I keep getting 1949 & 1930 for the
ones that should be 2049 & 2030.
Could someone help me with a formula to convert the dates to the MM/DD/
YYY format with the proper year.

First let me verify: the field in question is a Text field, not a Date/Time
field with its Format property set to "mmddyy"?

To interpret 2-digit years in date conversion, Access uses a built-in date
window that assumes 2-digit years less than 30 are in the 21st century,
while those 30 or greater are in the 20th century. It seems this is
resulting in a wrong interpretation for your purpose. What do you want to
use as the century cutoff? What about the mmddyy date can be used to tell
Access what century to put the date in?

 

[Sam]

First let me verify:  the field in question is a Text field, not a Date/Time
field with its Format property set to "mmddyy"?

To interpret 2-digit years in date conversion, Access uses a built-in date
window that assumes 2-digit years less than 30 are in the 21st century,
while those 30 or greater are in the 20th century.  It seems this is
resulting in a wrong interpretation for your purpose.  What do you wantto
use as the century cutoff?  What about the mmddyy date can be used to tell
Access what century to put the date in?

Thanks for the reply!!!!
No the field is truly text and not a date formatted as mmddyy.
I would say the century cut off would need to be the 2-digit year of
50, with 50 being 1950 and 49 being 2049.

Does that help?

Samantha

 

[Dirk Goldgar]

No the field is truly text and not a date formatted as mmddyy.
I would say the century cut off would need to be the 2-digit year of
50, with 50 being 1950 and 49 being 2049.

Does that help?

Yes. The following expression should expand the text date to a string in
mm/dd/yyyy format, using 50 as the cutoff:

Format([DateField], "00\/00\/" & IIf(Right([DateField], 2) < "50",
"\2\0", "\1\9") & "00")

Please note that that was all on one line when I posted it, though the
newsreader will almost certainly have brooken it onto two lines.

 

[Sam]

No the field is truly text and not a date formatted as mmddyy.
I would say the century cut off would need to be the 2-digit year of
50, with 50 being 1950 and 49 being 2049.

Does that help?

Yes.  The following expression should expand the text date to a string in
mm/dd/yyyy format, using 50 as the cutoff:

    Format([DateField], "00\/00\/" & IIf(Right([DateField], 2) < "50",
"\2\0", "\1\9") & "00")

Please note that that was all on one line when I posted it, though the
newsreader will almost certainly have brooken it onto two lines.

Thank you SO much, it worked great.