truncating data

[NPI]

Seems a simple question but can't get it to work.
I have a field in a table with 1070824 which is (1,yy,mm,dd) I want to drop
the one and format into a date. It is imported from an excel file and will be
on a monthly basis. Any help would be great.

Tom

 

[Jerry Whittle]

Assuming that it's a text file and you are doing it in a query:

TheDate: CDate(Mid([TheFeild],4,2) & "/" & Mid([TheFeild],6,2) & "/" &
Mid([TheFeild],2,2))

 

[NPI]

Worked great! Please disregard the previous post.
Tom

Assuming that it's a text file and you are doing it in a query:

TheDate: CDate(Mid([TheFeild],4,2) & "/" & Mid([TheFeild],6,2) & "/" &
Mid([TheFeild],2,2))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Seems a simple question but can't get it to work.
I have a field in a table with 1070824 which is (1,yy,mm,dd) I want to drop
the one and format into a date. It is imported from an excel file and will be
on a monthly basis. Any help would be great.

Tom

 

[Douglas J. Steele]

No offense, Jerry, but you're forgetting that not everyone uses mm/dd/yyyy
as their Short Date format. For users whose Short Date format is dd/mm/yyyy,
that won't work.

This might (although I'm not sure what DateSerial does with 2 digit years)

TheDate: DateSerial(Mid([TheField],2,2), Mid([TheField],4,2),
Mid([TheField],6,2))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Assuming that it's a text file and you are doing it in a query:

TheDate: CDate(Mid([TheFeild],4,2) & "/" & Mid([TheFeild],6,2) & "/" &
Mid([TheFeild],2,2))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Seems a simple question but can't get it to work.
I have a field in a table with 1070824 which is (1,yy,mm,dd) I want to
drop
the one and format into a date. It is imported from an excel file and
will be
on a monthly basis. Any help would be great.

Tom

 

[John Spencer]

DateSerial handles 2-digit years the same way as the date gets
interpreted when you enter a date with two-digit years. So 29 is 2029
and 97 is 1997. And 31 is 1931. If I remember correctly the cutoff is 30.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Douglas J. Steele]

DateSerial handles 2-digit years the same way as the date gets interpreted
when you enter a date with two-digit years. So 29 is 2029 and 97 is 1997.
And 31 is 1931. If I remember correctly the cutoff is 30.

Thanks for doing my homework for me John. <g>

In XP (and I would assume other OS), you can change the cutoff under
Regional Settings in the Control Panel, but yes, the default cutoff date is
30, so that you'd get 2029, 2030, 1931, 1932

 

[Jerry Whittle]

Hi Doug,

Good point. You'd think I'd remember that as I lived in England for 4 years!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

No offense, Jerry, but you're forgetting that not everyone uses mm/dd/yyyy
as their Short Date format. For users whose Short Date format is dd/mm/yyyy,
that won't work.

This might (although I'm not sure what DateSerial does with 2 digit years)

TheDate: DateSerial(Mid([TheField],2,2), Mid([TheField],4,2),
Mid([TheField],6,2))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Assuming that it's a text file and you are doing it in a query:

TheDate: CDate(Mid([TheFeild],4,2) & "/" & Mid([TheFeild],6,2) & "/" &
Mid([TheFeild],2,2))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Seems a simple question but can't get it to work.
I have a field in a table with 1070824 which is (1,yy,mm,dd) I want to
drop
the one and format into a date. It is imported from an excel file and
will be
on a monthly basis. Any help would be great.

Tom