Convert numeric field to date

[cchristensen]

Hello, I have a field that is currently formatted as numeric, i.e. 20071129
and need Access to recognize it as date, i.e. 11/29/2007. Tried simply
changing the format in table design, but didn't work. I know this is
probably really simple . . . any suggestions?

Thx.

 

[Jerry Whittle]

In a query:

TheDate: CDate(Mid("20071129",5,2)& "/" & Right("20071129",2) & "/" &
Left("20071129",4))

Replace "20071129" with [TheFieldName]

 

[Douglas J. Steele]

Easier is

TheDate: CDate(Format([TheFieldName], "0000-00-00"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In a query:

TheDate: CDate(Mid("20071129",5,2)& "/" & Right("20071129",2) & "/" &
Left("20071129",4))

Replace "20071129" with [TheFieldName]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello, I have a field that is currently formatted as numeric, i.e.
20071129
and need Access to recognize it as date, i.e. 11/29/2007. Tried simply
changing the format in table design, but didn't work. I know this is
probably really simple . . . any suggestions?

Thx.

 

[Jerry Whittle]

Very nice. I'll have to remember that one.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Easier is

TheDate: CDate(Format([TheFieldName], "0000-00-00"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In a query:

TheDate: CDate(Mid("20071129",5,2)& "/" & Right("20071129",2) & "/" &
Left("20071129",4))

Replace "20071129" with [TheFieldName]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello, I have a field that is currently formatted as numeric, i.e.
20071129
and need Access to recognize it as date, i.e. 11/29/2007. Tried simply
changing the format in table design, but didn't work. I know this is
probably really simple . . . any suggestions?

Thx.