date format

[DD]

i have a table that is imported from excel. the date is list as 4062008 for
04/06/08. is there a way i can convert this to 20080406 or at best
04/06/2008.

any help is appreciated

thanks

 

[John W. Vinson]

i have a table that is imported from excel. the date is list as 4062008 for
04/06/08. is there a way i can convert this to 20080406 or at best
04/06/2008.

any help is appreciated

thanks

What's the datatype of the field as imported - Text or Number? Either will
work but the technique is a bit different.

I'd suggest adding a Date/Time field to the table (in table design view) and
running an Update query to populate it. If the field is of Number type update
it to

DateSerial([datefield] \ 10000, [datefield] \ 100 MOD 100, [datefield] MOD
100)

assuming that 4062008 means April 6. Post back if it's a text field - doable
but a bit snarkier!

 

[Tom Wickerath]

Hi DD and John,

Post back if it's a text field - doable but a bit snarkier!

I was working up a possible solution, with the assumption that the imported
date was a text field. Here is a SELECT query I came up with, for a table
named "TableName" and a field named "FieldName". Make the appropriate
substitutions:

SELECT [FieldName],
Right([FieldName],4) & Format(Mid([FieldName],1,
Len([FieldName])-4),"0000")
AS ByYear,
CDate(Left(Format([FieldName],"00000000"),2) & "/" &
Mid(Format([FieldName],"00000000"),3,2) & "/" & Right([FieldName],4))
AS ByDate

FROM TableName
WHERE Len([FieldName]) Between 7 And 8;


Assumptions:
The imported text is always 7 or 8 characters in length.
The value corresponding to the day is always two characters.

DD: To try out the above SQL statement, copy your existing table with data,
renaming it as TableName. Rename the imported field as FieldName.
Alternatively, make the appropriate substitutions in the SQL statement for
your table and field names. Create a new query. Dismiss the Add Table dialog,
without adding any tables. Switch to SQL View (View | SQL View). You should
see the SQL keyword "SELECT;" highlighted. Clear this from the SQL View. Copy
the SQL statement and paste it into the SQL View. Then run the query.

If the SELECT query looks okay with your data, then you simply need to add a
new field to your table (either a text field, for "ByYear", or a Date/Time
field for "ByDate") and convert this SELECT query to an UPDATE query. Of
course, you should make sure to have a good backup of your database before
running any action query.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

What's the datatype of the field as imported - Text or Number? Either will
work but the technique is a bit different.

I'd suggest adding a Date/Time field to the table (in table design view) and
running an Update query to populate it. If the field is of Number type update
it to

DateSerial([datefield] \ 10000, [datefield] \ 100 MOD 100, [datefield] MOD
100)

assuming that 4062008 means April 6. Post back if it's a text field - doable
but a bit snarkier!

 

[DD]

tom

this work like a charm. i am very impressed.

thanks so much

Hi DD and John,

Post back if it's a text field - doable but a bit snarkier!

I was working up a possible solution, with the assumption that the
imported
date was a text field. Here is a SELECT query I came up with, for a table
named "TableName" and a field named "FieldName". Make the appropriate
substitutions:

SELECT [FieldName],
Right([FieldName],4) & Format(Mid([FieldName],1,
Len([FieldName])-4),"0000")
AS ByYear,
CDate(Left(Format([FieldName],"00000000"),2) & "/" &
Mid(Format([FieldName],"00000000"),3,2) & "/" & Right([FieldName],4))
AS ByDate

FROM TableName
WHERE Len([FieldName]) Between 7 And 8;


Assumptions:
The imported text is always 7 or 8 characters in length.
The value corresponding to the day is always two characters.

DD: To try out the above SQL statement, copy your existing table with
data,
renaming it as TableName. Rename the imported field as FieldName.
Alternatively, make the appropriate substitutions in the SQL statement for
your table and field names. Create a new query. Dismiss the Add Table
dialog,
without adding any tables. Switch to SQL View (View | SQL View). You
should
see the SQL keyword "SELECT;" highlighted. Clear this from the SQL View.
Copy
the SQL statement and paste it into the SQL View. Then run the query.

If the SELECT query looks okay with your data, then you simply need to add
a
new field to your table (either a text field, for "ByYear", or a Date/Time
field for "ByDate") and convert this SELECT query to an UPDATE query. Of
course, you should make sure to have a good backup of your database before
running any action query.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

What's the datatype of the field as imported - Text or Number? Either will
work but the technique is a bit different.

I'd suggest adding a Date/Time field to the table (in table design view)
and
running an Update query to populate it. If the field is of Number type
update
it to

DateSerial([datefield] \ 10000, [datefield] \ 100 MOD 100, [datefield] MOD
100)

assuming that 4062008 means April 6. Post back if it's a text field -
doable
but a bit snarkier!

 

[Tom Wickerath]

You're very welcome. Glad we could be of help to you!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________