Leading zeros

[sahafi]

I have a field in one table in different dB that I would like to use to match
data to another table in another dB. The field in table 1 has leading zeros
while the field on table 2 doesn't. I have insterted a new field/col in my
2nd table and used an update query as follows:
selected the new field (Customer_Nbr) then on the update to:
Right("00000000" & [Cust_Nbr],8) and in the criteriea field: Is Not Null.
Cust_Nbr is the field currently without leading zeros. I needed to have a
total of 8 digits, but it kept getting the message zero record to append.
But when I use a select query that mimic my 2nd table the function/formula
worked fine. I needed to link the updated table to a 3rd dB, so I don't want
to use a query then make a table and link that new table instead I'd prefer
to use my original table with the additional field and link it to my other
dB. What is it that I"m missing?

Thanks for all the help.

 

[Lord Kelvan]

in a query you can use the format command

format(cust_nbr,"00000000")

that should work

Regards
Kelvan

 

[sahafi]

Thanks Kelvan. But your method added "Cust_Nbr" to all records on that field.
I went back to my initial function:
Right("00000000" & [Cust_Nbr],8) and it worked after removing the criteria:
Is Not Null.

Thanks.

 

[Lord Kelvan]

sorry cust_nbr needs to be in []

Regards
Kelvan