Concatenating text & number fields dropping leading zeroes

[acgs]

Within one of my tables I have a text field and a number field. I would like to concatenate them. Although I know how to concatenate I have the following problem:

1. The number field is filled with a code from an Oracle database and contains some entries which contain leading 0's. These leading 0's are required to identify the code.
2. When the two fields are joined, Access is dropping the leading 0's from the code. The result is therefore wrong.
i.e. Text = AA8769 Number= 0073
After joining, result = AA876973 but should be AA87690073
3. If I inspect the number field in the table all codes are displayed correctly i.e. 0073.

If anyone knows how to solve this please help.

Cheers

 

[The_Doc_Man]

Concatenate the number using the format function to convert it to a string rather than a CStr function.

If you are not familiar with Format$, look it up in the help file. Follow the "see also" threads to the one that talks about custom user formats for numbers. That will tell you how to force the number to retain leading zeros.

 

[gguy]

Similar problem

I am using Office XP Pro and am not finding the format$ function in the help. Do I need to add a reference?

The old manual invoice system we are using records invoice number as LB0013. I can get the right 4 characters and add 1 but when I concatenate 0014 with LB I get LB14. Can I correct this with format$? GGuy