How to add leading zeros for 12 char length

[catguy]

I have 2 tables, each with a field named "Lots", which are text data types.
The problem I have, is that the data for each table comes from a different data source, each using the same "lot number", except the character length from table 1 varies from 8 characters to 10 characters, depending on the number, and the character length in table 2 is a consistent 12 characters. (i.e. table 1: 100000325 and table 2: 000010000325)
In table 2, there are "leading zeros" added to the number to make it 12 characters in length.
Unfortunately, the data source adds these automatically to one of the data dumps, but I cannot change it to do this for the other data dump, so I need to do it in access.
How can I add leading zeros to the "Lots" field in table 1 so that it matches the 12 characters of table 2?
It can't be that difficult, but I'm at a loss......Thanks

[pbaldy]

You can use the Format() function:

Format(FieldName, "000000000000")

[catguy]

Thanks Baldy, but I tried that earlier. It only returns those records that already contain 12 characters, and of the 3,768 records, only 370 of them have the full 12 chars.
Any other thoughts?
I need to add enough zeros to the existing number to bring up to 12 chars. ("100000325" [8 chars] or "890000016" [9 chars] needs to be changed to "000010000325" or "000890000016")

[maximus]

Try This:
String(12-(Len([Lots])),"0") & [Lots]
This will add sufficient no.of.zeros to make all the records have 12 characters.
if this solves your problem mark this thread solved.

[pbaldy]

That is a commonly used method to pad zeros. It sounds like you may have used it as a criteria instead of a field.

Thanks Baldy, but I tried that earlier. It only returns those records that already contain 12 characters, and of the 3,768 records, only 370 of them have the full 12 chars.
Any other thoughts?
I need to add enough zeros to the existing number to bring up to 12 chars. ("100000325" [8 chars] or "890000016" [9 chars] needs to be changed to "000010000325" or "000890000016")

[catguy]

That is a commonly used method to pad zeros. It sounds like you may have used it as a criteria instead of a field.

You are absolutely correct...... and after I got over my Homer Simpson "DOH", I realized that I had tried using it the criteria.
Using it the field worked just as it should. It's been a long, long week.... Thanks

[pbaldy]

No problemo; we all have our "DOH" moments. Though I seem to have more than my fair share.