Formatting - Key Field

[Joe]

I have a field called "ID". It is a KEY field and
AUTONUMBERED. I need to FORMAT the ID field so that the
numbers are padded with zeros to make it a nine digit
number (e.g. 1 would be 000000001).

I used the FORMAT field in table design to input the 9
zeros. The resulting display of the data correctly
reflects the numbers as 000000001, 000000002, etc.

A problem occurs when I try to EXPORT the file to a
DELIMITED TEXT file. The resulting record output is not
the expected 000000001, but (SPACES) 1

How can I define the field so that it exports the field
correctly reflecting the 000000001.

Any advice would be appreciated!

 

[RobFMS]

This is a response to a previous post I created. Let me know if it helps.

==========================
From: RobFMS (Rob@_DropThis_fmsinc.com)
Subject: Re: Autonumber format 0000
View: Complete Thread (3 articles)
Original Format
Newsgroups: microsoft.public.access
Date: 2003-04-05 13:02:27 PST

Ken

Ken

In table design view, select the field that will be the Autonumber field.
On the "General" tab on the bottom, enter the following in the "Format"
property : 000000

HTH

--
Rob

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-Having trouble with your database?
-Are you ready to upgrade, but don't have the expertise?
Let the FMS Professional Solutions Group solve it for you.
www.FMSInc.com/Consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==========================

--

Rob

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Problems with your database? Need to upgrade your application?
Contact the FMS Professional Solutions Group: www.fmsinc.com/consulting

Need a Book Recommendation?
www.fmsinc.com/toplevel/books.htm

Need software tools for Access, VB or .NET?
http://www.fmsinc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Steve Schapel]

Joe,

The format property of the table field does not modify the data, but
simply affects the way it is displayed.

I think in the query that you use to export the data you will need to
modify the data using the Format() function (confusing, I know, as it
is a differnt usage of the word 'format'). Something like...
ExpandedKey: Format([ID],"000000000")

- Steve Schapel, Microsoft Access MVP

 

[Joe]

Thanks guys...

and Steve... I was playing with that just yesterday and I
was able to export the single field in question to a new
table... using the following query...I found this in a
Knowledge Base Article...and modified it:

SELECT DISTINCTROW
Format([ID],"000000000" AS [Expr1]
INTO [NEWTABLE]
FROM [BBL];

The only problem is that is ONLY exports the one field (ID)
into the new table.. I want ALL the fields in the new
table... which I then want to export to an ASCII Text
file... ANY SUGGESTIONS ??

Thanks again !

-----Original Message-----
Joe,

The format property of the table field does not modify the data, but
simply affects the way it is displayed.

I think in the query that you use to export the data you will need to
modify the data using the Format() function (confusing, I know, as it
is a differnt usage of the word 'format'). Something like...
ExpandedKey: Format([ID],"000000000")

- Steve Schapel, Microsoft Access MVP

I have a field called "ID". It is a KEY field and
AUTONUMBERED. I need to FORMAT the ID field so that the
numbers are padded with zeros to make it a nine digit
number (e.g. 1 would be 000000001).

I used the FORMAT field in table design to input the 9
zeros. The resulting display of the data correctly
reflects the numbers as 000000001, 000000002, etc.

A problem occurs when I try to EXPORT the file to a
DELIMITED TEXT file. The resulting record output is not
the expected 000000001, but (SPACES) 1

How can I define the field so that it exports the field
correctly reflecting the 000000001.

Any advice would be appreciated!

.

 

[Steve Schapel]

Joe,

You only have to include your other fields into the query. Like...
SELECT DISTINCTROW
Format([ID],"000000000" AS [Expr1], BBL.YourNextField,
BBL.AndAnotherOne, BBL.Etc
INTO [NEWTABLE]
FROM [BBL];

My other suggestion is that what is the purpose of NEWTABLE? There is
no need for a Make-Table query, as you can export to your text file
directly from a select query...
SELECT Format([ID],"000000000" AS [Expr1], BBL.YourNextField,
BBL.AndAnotherOne, BBL.Etc
FROM [BBL];

- Steve Schapel, Microsoft Access MVP

Thanks guys...

and Steve... I was playing with that just yesterday and I
was able to export the single field in question to a new
table... using the following query...I found this in a
Knowledge Base Article...and modified it:

SELECT DISTINCTROW
Format([ID],"000000000" AS [Expr1]
INTO [NEWTABLE]
FROM [BBL];

The only problem is that is ONLY exports the one field (ID)
into the new table.. I want ALL the fields in the new
table... which I then want to export to an ASCII Text
file... ANY SUGGESTIONS ??

Thanks again !

-----Original Message-----
Joe,

The format property of the table field does not modify the data, but
simply affects the way it is displayed.

I think in the query that you use to export the data you will need to
modify the data using the Format() function (confusing, I know, as it
is a differnt usage of the word 'format'). Something like...
ExpandedKey: Format([ID],"000000000")

- Steve Schapel, Microsoft Access MVP

I have a field called "ID". It is a KEY field and
AUTONUMBERED. I need to FORMAT the ID field so that the
numbers are padded with zeros to make it a nine digit
number (e.g. 1 would be 000000001).

I used the FORMAT field in table design to input the 9
zeros. The resulting display of the data correctly
reflects the numbers as 000000001, 000000002, etc.

A problem occurs when I try to EXPORT the file to a
DELIMITED TEXT file. The resulting record output is not
the expected 000000001, but (SPACES) 1

How can I define the field so that it exports the field
correctly reflecting the 000000001.

Any advice would be appreciated!

.