Date conversion

[gal]

Hi group,

I have a .txt file containing dates. Access is unable to
import the date format, that's why I convetered all dates
to numbers and through Format I got from
20001111 to 2000/11/11.

Now the problem. I have to convert the dates from
2000/11/11 to 11/11/2000. Of course I have no clue how
to do that. My text file has dates in format 20001111.

Any ideas ? Help is appreciated.

Galin

 

[John Nurick]

Hi Galin,

Assuming you have imported your "20001111" dates into a text field in
Access, go like this:

1) add a date/time field to the table.
2) create an update query to update the new field to
DateSerial(Left([D],4), Mid([D], 5,2), Right([D],2))
where D is the name of the text field.

Once the dates are in a date field you can display them any way you like
by using the Format property of the field or textboxes. Or you can
export them to a text file in any format by using the Format() function
in an query and exporting the query.

 

[Guest]

Thank John,

I have imported my dates into numeric field in Acccess.
When I try to convert the field from Number to Date/Time ,
Access deletes the records in the entire field. Do you
suggest creating a new blank field Date/Time and then
using Update Query?

-----Original Message-----
Hi Galin,

Assuming you have imported your "20001111" dates into a text field in
Access, go like this:

1) add a date/time field to the table.
2) create an update query to update the new field to
DateSerial(Left([D],4), Mid([D], 5,2), Right ([D],2))
where D is the name of the text field.

Once the dates are in a date field you can display them any way you like
by using the Format property of the field or textboxes. Or you can
export them to a text file in any format by using the Format() function
in an query and exporting the query.

Hi group,

I have a .txt file containing dates. Access is unable to
import the date format, that's why I convetered all dates
to numbers and through Format I got from
20001111 to 2000/11/11.

Now the problem. I have to convert the dates from
2000/11/11 to 11/11/2000. Of course I have no clue how
to do that. My text file has dates in format 20001111.

Any ideas ? Help is appreciated.

Galin

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Either way you need to add a date-time field and use an update query. If
the "20001111" dates are in a Numeric(Long) field, replace each
[D]
in the example below with
Cstr([D])
where is the name of the numeric field.

Thank John,

I have imported my dates into numeric field in Acccess.
When I try to convert the field from Number to Date/Time ,
Access deletes the records in the entire field. Do you
suggest creating a new blank field Date/Time and then
using Update Query?

-----Original Message-----
Hi Galin,

Assuming you have imported your "20001111" dates into a text field in
Access, go like this:

1) add a date/time field to the table.
2) create an update query to update the new field to
DateSerial(Left([D],4), Mid([D], 5,2), Right ([D],2))
where D is the name of the text field.

Once the dates are in a date field you can display them any way you like
by using the Format property of the field or textboxes. Or you can
export them to a text file in any format by using the Format() function
in an query and exporting the query.

Hi group,

I have a .txt file containing dates. Access is unable to
import the date format, that's why I convetered all dates
to numbers and through Format I got from
20001111 to 2000/11/11.

Now the problem. I have to convert the dates from
2000/11/11 to 11/11/2000. Of course I have no clue how
to do that. My text file has dates in format 20001111.

Any ideas ? Help is appreciated.

Galin

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[david epsom dot com dot au]

a: cdate(format(...))

(david)