Convert text datatype "20101021" to date yyyymmdd 1

[cdgeer]

Could someone please show how I would convert a text string such as "20101021" to a date format and datatype such as 10/21/2010 ? I've been toying with some VB code but not successful. Thanks for any help.

 

[SkipVought]

hi,

YourRealDate = DateSerial(Left[YourField],4),Mid[YourField],5,2),Right[YourField],4))

Dates are pure numbers that can be FORMATTED to DISPLAY in a number of different formats at report time, using the Format function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdgeer]

Ahhh, You have to separate the yyyy, mm, dd . Thanks so much!!

 

[Andrzejek]

Or you could do this:

Dim strADate As String
Dim datMyDate As Date

strADate = "20101021"

datMyDate = CDate(Mid(strADate, 5, 2) & "/" & Right(strADate, 2) & "/" & Left(strADate, 4))

MsgBox datMyDate

But I would go with Skip's code.....

Have fun.

---- Andy

 

[cdgeer]

I'm running into a snag. I'm getting a datatype mismatch. I looked up DateSerial() since I wasn't familiar with it. It said that DateSerial expects an integer. My "YourField" as Skip called it, is a text datatype. Would that be the problem? If so, how can I convert to integer?
The 20101021 is actually part of a table name like:
"20101021_All_Personnel" so I changed skip's statement slightly to:
YourRealDate = DateSerial(Left([YourField],4),Mid([YourField],5,2),Mid([YourField],7,2))

 

[SkipVought]

well you specifically asked, "how I would convert a text string such as "20101021" to a date format?"

Glad you figured it out on your own.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}