Urgent Data Convert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I dont know if anyone can help but I have been given some data to deal with
and unfortunately it's in a text format,

I have a date that looks like 01122005 as a date but is in table with a type
as text, I am not allowed to change the original table so I have built a
query that looks at the table, what I want to do is be able to sort by the
date, I have converted it using the CDbl command to convert it to numbers but
I am not sure where to go from there, does anyone have any ideas.

Thanks in advance
Phil
 
Hi Phil
I will probably get slated by the pros here, but here goes
Create a new field in your query and enter the following in the Field header

expr1: Left([Date],2) & "/" & Mid([Date],3,2) & "/" & Right([Date],4)

It works ok on a test DB.

HTH

John
 
Do you need the date values for further calculations / comparison or you
simply to display them in another format?

Assuming the format of the data in the Table represent "ddmmyyyy" (your
sample is ambiguous) and you want to get the date VALUE, create a Calculated
Field:

MyDate: DateSerial(Right([DateText],4), Mid(([DateText],3,2),
Left(([DateText],2) )

which gives you the correct date value and you can format to whichever
format you like.
 
Hi Van,

Thanks for the reply, yes I will need to dates to do further analysis, can
you tell me how do I set up a calculated field?

Thanks Phil

Van T. Dinh said:
Do you need the date values for further calculations / comparison or you
simply to display them in another format?

Assuming the format of the data in the Table represent "ddmmyyyy" (your
sample is ambiguous) and you want to get the date VALUE, create a Calculated
Field:

MyDate: DateSerial(Right([DateText],4), Mid(([DateText],3,2),
Left(([DateText],2) )

which gives you the correct date value and you can format to whichever
format you like.

--
HTH
Van T. Dinh
MVP (Access)



Phil said:
Hi all,

I dont know if anyone can help but I have been given some data to deal
with
and unfortunately it's in a text format,

I have a date that looks like 01122005 as a date but is in table with a
type
as text, I am not allowed to change the original table so I have built a
query that looks at the table, what I want to do is be able to sort by the
date, I have converted it using the CDbl command to convert it to numbers
but
I am not sure where to go from there, does anyone have any ideas.

Thanks in advance
Phil
Click to expand...
Click to expand...
 
In the query grid
--click in a new column
--Copy and paste Van's code into the field "cell"
--Change [DateText] to the name of your field.

Phil said:
Hi Van,

Thanks for the reply, yes I will need to dates to do further analysis, can
you tell me how do I set up a calculated field?

Thanks Phil

Van T. Dinh said:
Do you need the date values for further calculations / comparison or you
simply to display them in another format?

Assuming the format of the data in the Table represent "ddmmyyyy" (your
sample is ambiguous) and you want to get the date VALUE, create a
Calculated
Field:

MyDate: DateSerial(Right([DateText],4), Mid(([DateText],3,2),
Left(([DateText],2) )

which gives you the correct date value and you can format to whichever
format you like.

--
HTH
Van T. Dinh
MVP (Access)



Phil said:
Hi all,

I dont know if anyone can help but I have been given some data to deal
with
and unfortunately it's in a text format,

I have a date that looks like 01122005 as a date but is in table with a
type
as text, I am not allowed to change the original table so I have built
a
query that looks at the table, what I want to do is be able to sort by
the
date, I have converted it using the CDbl command to convert it to
numbers
but
I am not sure where to go from there, does anyone have any ideas.

Thanks in advance
Phil
Click to expand...
Click to expand...
Click to expand...
 
Back
Top