Problem with Formatting for Microsoft Query Tables

[andyarg19]

Good afternoon all,
thanks in advance if anyone is able to help me out.

I have a problem at work. I have a set of distributions; the distro's are made up of 10 columns. One of them being 'UPC' which is a 12 or 13 digit number. Basically, I have a master file with all the distro's that can go to 20 different divisions across the US and each of those divisions will have their own file for their own distro's. So one master file that feeds 20 other files. Sometimes, we have to make updates to the distro's for whatever reason; so i decided to use Microsoft query so that when an update was made to the master file, that it automatically feeds those changes into the files that it needs to go to.

I have done this successfully. The tables come over nice through the Microsoft query, that isn't my problem. The problem I am facing is that when the query feeds the other files, it brings the UPC as an Exponential #. This is weird because in the main master table, the UPCs are showing correctly and are showing the entire 12 or 13 digit number. I select the #s in the new table and format them as a number, it doesn't change anything. I format them as a text, still no change. It seems when the query pulls the info it is changing how the data is stored or formatted and for the life of me I cannot change it to the way I want. The #s are not being formatted as the full 12 or 13 digit number, which is what I really need to show.

Any ideas? Is the fact that I am working in a table changing things?

 

[programsam]

Can you bring it in and then choose 'Custom' formatting for the UPC column on the other files with thirteen consecutive 0's in the 'Type' field?

 

[andyarg19]

That does not work for 90% of them. There were a few at the top that i individually went into and hit enter and they turned back into proper numbers. The rest did not change. Idk if maybe it has to do with some type of settings i have for my excel that it changes the format on its own without me telling it. Thanks.

 

[programsam]

That does not work for 90% of them. There were a few at the top that i individually went into and hit enter and they turned back into proper numbers. The rest did not change. Idk if maybe it has to do with some type of settings i have for my excel that it changes the format on its own without me telling it. Thanks.

Odd, the only other thing that I can think of is maybe adding a custom column in PQ that multiplies the # by 1 to force a whole number before passing it through to the tables.