|
Home > Archive > MS Access database support > July 2005 > how to pad zeros to the left of a field in a table and KEEP them there?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
how to pad zeros to the left of a field in a table and KEEP them there?
|
|
| tpcolson@gmail.com 2005-07-29, 9:24 am |
|
I have a fairly large access 2003 table (200,000) records. Field 'X' is
a text field, and contains either no value, 4 digits, 5 digits, or 6
digits. What I need to do is to add two zeros to the left of the
records with 4 digits in field 'X', 1 zero to the left of the records
with 5 digits in field 'X', and 6 digits for nulls. IE, record 1 field
'X' contains 1234, after this magic, it would contain 001234. Bear in
mind, I have ABSOLUTLY no programing experience and I think SQL is a
star wars character. Was wondering if there was an easier way of doing
this?
oh yeah, tried "000000" in the "Format" Field in table design view.
While that does a great job of padding zeros to the left, it only
displays them. When I do append queries using that field, it doens't
read the padded zeros. Any ideas? THanks.
| |
| Rick Brandt 2005-07-29, 9:24 am |
| tpcolson@gmail.com wrote:
> I have a fairly large access 2003 table (200,000) records. Field 'X'
> is
>
> a text field, and contains either no value, 4 digits, 5 digits, or 6
> digits. What I need to do is to add two zeros to the left of the
> records with 4 digits in field 'X', 1 zero to the left of the records
> with 5 digits in field 'X', and 6 digits for nulls. IE, record 1 field
> 'X' contains 1234, after this magic, it would contain 001234. Bear in
> mind, I have ABSOLUTLY no programing experience and I think SQL is a
> star wars character. Was wondering if there was an easier way of doing
> this?
You need an update query. I can only give you that in a newsgroup post as
SQL, but you can paste it into the SQL view of a new query and then switch
to the graphical design view. (please test on a copy of your table first)
UPDATE TableName
SET [TableName]![X] = Format(Val([TableName]![X]), "000000")
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
| |
| tpcolson@gmail.com 2005-07-29, 9:25 am |
| Thanks for the tip. I tried that, and get
"...couldn't update 10121 fields due to type conversion failure...."
The other 190000 fields weren't padded with any zeros at all.
Hmmmm....
| |
| Rick Brandt 2005-07-29, 11:25 am |
| tpcolson@gmail.com wrote:
> Thanks for the tip. I tried that, and get
> "...couldn't update 10121 fields due to type conversion failure...."
>
> The other 190000 fields weren't padded with any zeros at all.
>
> Hmmmm....
If you create a simple SELECT query with your table as the input source and
add a calculated field with...
MyTestOutput: Format(Val([TableName]![X]), "000000")
....does the output look correct? Are you sure the field that you want to
update is a text field and not a numeric field?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
| |
| David W. Fenton 2005-07-29, 8:25 pm |
| "Rick Brandt" <rickbrandt2@hotmail.com> wrote in
news:WNrGe.865$gQ5.799@newssvr33.news.prodigy.com:
> tpcolson@gmail.com wrote:
>
> If you create a simple SELECT query with your table as the input
> source and add a calculated field with...
>
> MyTestOutput: Format(Val([TableName]![X]), "000000")
>
> ...does the output look correct? Are you sure the field that you
> want to update is a text field and not a numeric field?
Are there, perhaps, 10,121 records with NULL value in the field?
I would be sure to run the update query with a WHERE [yourfield] Is
Not Null.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
|
|
|
|
|