M
Maggie
Platform MS Access 2000
Description:
We are getting external data by importing a text file to MS Access. The
text file contains all the
dates such as :
01-01-1943
08-16-1987
08-03-1949
03-20-1949
06-07-1937
11-16-1953
12-12-1966
03-27-1957
07-28-1945
12-16-1933
03-25-1950
07-08-1955
The Access Table structure is text field:
TABLE [test] (
[field7] text
)
When text file is imported into Access, the data format changed
automatically for some reason, Here's the Sample data in the table are:
field7
----------
1/1/1943
8/16/1987
8/3/1949
3/20/1949
6/7/1937
11/16/1953
12/12/1966
3/27/1957
7/28/1945
12/16/1933
3/25/1950
7/8/1955
8/10/1944
8/28/1956
11/18/1973
10/29/1952
2/3/1950
5/30/1969
You may see that Access replaced '-' with '/' and emlinated leading zero.
We are not allowed to change the data type from text to date/time because of
front end application.
We are not allowed to delete existing data to re-import again.
Instead, we must format the data by padding leading zeros and using "-" date
field separator. For instance, replace "/" with "-" and add leading zeros to
single digit date fields, such as, 07-07-1943 instead of 7/7/1943, or replace
12/3/1937 with 12-03-1937.
Is there setting that I can perform this convertion within MS access? Thank
you.
Description:
We are getting external data by importing a text file to MS Access. The
text file contains all the
dates such as :
01-01-1943
08-16-1987
08-03-1949
03-20-1949
06-07-1937
11-16-1953
12-12-1966
03-27-1957
07-28-1945
12-16-1933
03-25-1950
07-08-1955
The Access Table structure is text field:
TABLE [test] (
[field7] text
)
When text file is imported into Access, the data format changed
automatically for some reason, Here's the Sample data in the table are:
field7
----------
1/1/1943
8/16/1987
8/3/1949
3/20/1949
6/7/1937
11/16/1953
12/12/1966
3/27/1957
7/28/1945
12/16/1933
3/25/1950
7/8/1955
8/10/1944
8/28/1956
11/18/1973
10/29/1952
2/3/1950
5/30/1969
You may see that Access replaced '-' with '/' and emlinated leading zero.
We are not allowed to change the data type from text to date/time because of
front end application.
We are not allowed to delete existing data to re-import again.
Instead, we must format the data by padding leading zeros and using "-" date
field separator. For instance, replace "/" with "-" and add leading zeros to
single digit date fields, such as, 07-07-1943 instead of 7/7/1943, or replace
12/3/1937 with 12-03-1937.
Is there setting that I can perform this convertion within MS access? Thank
you.