Union query is dropping leading zeros...

[Pack10]

I have a tavle with a field that is numeric 12 length with some leading zeros. I have an item with a value in the table 009945973000.

I have another table where the same named field is text.
I create a UNION query to "glue" the two tables.
(these come in as spread sheets).

When I run the UNION query, the value shows as 9945973000 its dropping the leading zeros. How can I solve this.
I need the full value.

 

[SkipVought]

Hi,

Numbers (NUMERIC fields) do not have leading zeros.

Character fields do. Use the Format function to convert your numbers to a string of whatever length you desire.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

FYI, faq68-6659

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Pack10]

Hi
Thanks for getting back. Where would I do this,,,,an update query or in the table designer.

 

[SkipVought]

Is your "number" a value that you do arithmetic on, or is it just an identifier, like an Employeed id or Invoice id?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Pack10]

Its an identifier. I got it, In the UNION query I used SELECT FORMAT([FIELD],"000000000000") and it worked.

Thanks much, I appreciate ....

 

[SkipVought]

your identifier fields really ought to be TEXT rather than a numeric type.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]