Zero Fill

[Guest]

Does Access have a way to zero fill every record of a specific variable up to
a specified length?

For example, I have a table that looks like:

12345
123456
12345678

And I would like to get it like this:

00012345
00123456
12345678

Thanks.

 

[Thomas Winkler]

Hi,

Does Access have a way to zero fill every record of a specific variable up to
a specified length?

For example, I have a table that looks like:

12345
123456
12345678

And I would like to get it like this:

00012345
00123456
12345678

If your field contains numeric values then it should be enough just to
_display_ these zeros. change the format-property of your field to
"00000000".

HTH

Thomas

 

[Baz]

Does Access have a way to zero fill every record of a specific variable up to
a specified length?

For example, I have a table that looks like:

12345
123456
12345678

And I would like to get it like this:

00012345
00123456
12345678

Thanks.

Good grief, it seems like everyone today wants to take perfectly good
numbers and turn them into text strings with leading zeros!

Format(SomeNumber, "00000000")

 

[Guest]

Baz... Where would I put that code? Somewhere in the 'design view' for the
table?

Thanks.

 

[Baz]

Baz... Where would I put that code? Somewhere in the 'design view' for the
table?

Thanks.

No. I don't know where you would use it, you are really going to have to
explain why it is you want to do this.

 

[Guest]

These numbers are unique ID's that I'm trying to match to another table. The
entries in the other table were and always have been formatted as text so
they have their leading zero's still appended. However, in this table they
were formatted as a number at some point so the leading zero's were stripped
off. For example:

When trying to match "12345" to "000012345" Access would call this a
non-match, so I need to attach 4 0's to the "12345" before the query can be
run.

Does that make a little more sense?
Thanks.

 

[Baz]

These numbers are unique ID's that I'm trying to match to another table. The
entries in the other table were and always have been formatted as text so
they have their leading zero's still appended. However, in this table they
were formatted as a number at some point so the leading zero's were stripped
off. For example:

When trying to match "12345" to "000012345" Access would call this a
non-match, so I need to attach 4 0's to the "12345" before the query can be
run.

Does that make a little more sense?
Thanks.

Hi Dave,

Yes, that's the kind of info that was needed. You have two choices:

1. Permanently change the values in the Access table. To do this, first
go into table design and change the field from numeric to text. Then,
create and run a query like this:

UPDATE sometable SET somefield = Format(Clng(somefield),"000000000")

2. However, I don't recommend option 1. Far better would be to create
and save a query that selects all the fields from your table EXCEPT, instead
of the ID field, it selects this:

Format(somefield,"000000000")

Then, use the query instead of the table in your comparison operations.

Obviously in the above you would need to replace "sometable" and "somefield"
with the actual names you are using.