Text to Number Query

[bluedemon186]

Hello,

I have a linked table to Access 2010 which contains tracking numbers for shipments.

United Postal Service tracking#s is Alpha Numerical and FedEx is just Numerical.

When I link the spreadsheet to the Table, the FedEx tracking numbers are changed to for example - 9.813842152e+014 instead of 981384215196229. Is there anyway to change these back to its original format? I was thinking of creating a query where I could use Conversion Function, but it kept giving me error. Please note that both the UPS Tracking and FedEx tracking are in the same field. Any help would be greatly appreciated.

 

[rzw0wr]

You can change them for display only. I don't see a way to change the field type without getting into problems with the UPS tracking numbers.

A field can have only 1 data type.

Dale

 

[Isskint]

on the spreadsheet set the cell format of the tracking number to "Text" (the same in the database). When you import it will see the data as text

 

[bluedemon186]

Hi rzw0wr,
Thank you for the feedback. I understand I cannot have two different data types, but is there a way to convert the number back to what it originally was? Thru a form or query maybe? The data type would still stay as text.

Hi Isskint,

Thank you for the feedback. I already tried that. When I do that, the FedEx tracking number change in the excel spreadsheet so I run into the same issue.

 

[rzw0wr]

I don't know about a query.
With code you could change it to number using Val() the back to a string using Str()
I am not sure how 9.813842152e+014 will react to Val().

Dale

 

[bluedemon186]

Hi Pat,

The issue I am running into is that both the UPS and FedEx Tracking are in the same column. The spreadsheet gets updated daily and its a new file with the same file name so I don't have to link it everyday.

I tried using Clng (Expression), Val (Expression) in a query and a form, but I did not get a desired result. So is there a formula or a code that would change 9.813842152e+014 back to 981384215196229?

 

[bluedemon186]

Thank you, Pat. I tried something similar yesterday. I changed the data type to text before linking it to access so access thinks its a text field. Then I went back to excel and change the data type back to number which changed the FedEx Tracking to what I wanted. It is working as of right now. Now, I just have to see if it stays the same when a new report has been saved at the end of today.