Format data field with leading zero and replace separator

[Maggie]

Platform MS Access 2000 in win2k server
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.

 

[KARL DEWEY]

Just format the display like --
Format([YourField], "mm-dd-yyyy")

 

[Maggie]

As I mentioned, the field is text data type. It is NOT date/time. If I can
use mm-dd-yyyy on TEXT data type, I will not post my question here. Please
read my post.

Just format the display like --
Format([YourField], "mm-dd-yyyy")

Platform MS Access 2000 in win2k server
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.

 

[John Vinson]

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.

I have no idea how the import process recast the data as date/times
(it IS a Text field in the Access table... right? Maybe you should
doublecheck!)

Try running an Update query parsing it back: update [fieldname] to

Format(CDate([Fieldname], "mm-dd-yyyy"))

CDate will interpret the string as a date; Format() will cast it back
as a string in the format you desire.

John W. Vinson[MVP]