How do I convert 20051231 to 12/31/2005?

[weerdsteev]

I've got an SQL databse I'm importing into Access that stores dates in
yyyymmdd format. I need to convert that to standard mm/dd/yy or mm/dd/yyyy.
Don't know how to do it and don't know visual basic. Shall I abandon all
hope...or can someone tell me how to use the Expression Builder to do
this...? Thanks!

 

[Ofer]

try this
cvdate(right(FieldName,2) & "/" & mid(FieldName,5,2) & "/" &
left(FieldName,4))

 

[John Vinson]

I've got an SQL databse I'm importing into Access that stores dates in
yyyymmdd format. I need to convert that to standard mm/dd/yy or mm/dd/yyyy.
Don't know how to do it and don't know visual basic. Shall I abandon all
hope...or can someone tell me how to use the Expression Builder to do
this...? Thanks!

Pretty easy actually:

CDate(Format([textdate], "@@@@-@@-@@"))


John W. Vinson[MVP]

 

[Ofer]

This is much better

I've got an SQL databse I'm importing into Access that stores dates in
yyyymmdd format. I need to convert that to standard mm/dd/yy or mm/dd/yyyy.
Don't know how to do it and don't know visual basic. Shall I abandon all
hope...or can someone tell me how to use the Expression Builder to do
this...? Thanks!

Pretty easy actually:

CDate(Format([textdate], "@@@@-@@-@@"))


John W. Vinson[MVP]

 

[Someone]

Hi John

Could you explain further how this works, please?

Thanks
M

I've got an SQL databse I'm importing into Access that stores dates in
yyyymmdd format. I need to convert that to standard mm/dd/yy or
mm/dd/yyyy.
Don't know how to do it and don't know visual basic. Shall I abandon all
hope...or can someone tell me how to use the Expression Builder to do
this...? Thanks!

Pretty easy actually:

CDate(Format([textdate], "@@@@-@@-@@"))


John W. Vinson[MVP]

 

[Douglas J Steele]

Using @@@@-@@-@@ tells the Format function to show the first 4 characters of
the string, then a dash, then the next 2 characters, another dash, then
finally the next 2 characters. That means the string of 20051231 will be
converted to a string of 2005-12-31, which is a recognizable date format
that CDate will convert.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John

Could you explain further how this works, please?

Thanks
M

I've got an SQL databse I'm importing into Access that stores dates in
yyyymmdd format. I need to convert that to standard mm/dd/yy or
mm/dd/yyyy.
Don't know how to do it and don't know visual basic. Shall I abandon all
hope...or can someone tell me how to use the Expression Builder to do
this...? Thanks!

Pretty easy actually:

CDate(Format([textdate], "@@@@-@@-@@"))


John W. Vinson[MVP]

 

[Someone]

Thanks Doug!

Using @@@@-@@-@@ tells the Format function to show the first 4 characters of
the string, then a dash, then the next 2 characters, another dash, then
finally the next 2 characters. That means the string of 20051231 will be
converted to a string of 2005-12-31, which is a recognizable date format
that CDate will convert.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John

Could you explain further how this works, please?

Thanks
M

I've got an SQL databse I'm importing into Access that stores dates in
yyyymmdd format. I need to convert that to standard mm/dd/yy or
mm/dd/yyyy.
Don't know how to do it and don't know visual basic. Shall I abandon all
hope...or can someone tell me how to use the Expression Builder to do
this...? Thanks!

Pretty easy actually:

CDate(Format([textdate], "@@@@-@@-@@"))


John W. Vinson[MVP]

 

[Someone]

Hi Doug - many thanks for your help!

Using @@@@-@@-@@ tells the Format function to show the first 4 characters
of
the string, then a dash, then the next 2 characters, another dash, then
finally the next 2 characters. That means the string of 20051231 will be
converted to a string of 2005-12-31, which is a recognizable date format
that CDate will convert.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John

Could you explain further how this works, please?

Thanks
M

On Wed, 7 Dec 2005 09:33:03 -0800, "weerdsteev"

I've got an SQL databse I'm importing into Access that stores dates in
yyyymmdd format. I need to convert that to standard mm/dd/yy or
mm/dd/yyyy.
Don't know how to do it and don't know visual basic. Shall I abandon all
hope...or can someone tell me how to use the Expression Builder to do
this...? Thanks!

Pretty easy actually:

CDate(Format([textdate], "@@@@-@@-@@"))


John W. Vinson[MVP]