Convert text to number data type in Access 2007

Hi Guys,

I'm working on converting a large table with a field that has numeric values in text data type. When I do the conversion in Access automatically some records don't get converted. The reason why is because the field that holds values in the table has the following data ( 9.22337E+18 ) now this happened because the data was imported from Excel file. The challenge now is to convert that type of number to a whole number such as: 9223370000000000000
Is there any function that takes care of this type of scenario?

Thanks!

JoeKid09

re: Convert text to number data type in Access 2007

a number colmn in excel in being imported into access as text?

I believe that when you import from excel into access, the datatype is calulated from the values in the first row that is not selected as a field names row.

so

If you ensure that, before doing the import, the first 'data' row in excel contains data that can only be interpreted by access with the datatype you intend then that should work.

At least, it has for me in the past when I have suffered simmilar issues importing from excel

re: Convert text to number data type in Access 2007

Alternatively, using the same method outlined by Delerna, you can use the Text data type to import it, as all other data types can quite happily be held as text. Convert it back to whatever you need from within Access.