convert field data type from text to number

[ajm]

how does one convert a field from text to numbers in access? in my table, one field for customer numbers is stored as text. it contains proper numbers and also "text" numbers where the number starts with an alpha character. I have tried to change the format of the field in the table set up. I have tried to multiply the customer numbers by 1 to create real numbers.

but i keep losing data. I am currently trying to write an "iif" function to the effect that if the customer number starts with an alpha, leave it as is, else multiply by one.

Can anyone please help with a solution?

ajm

 

[xenou]

A low tech solution is to use an import template that contains dummy data (Text, integer, decimal, or date, as needed, in the first row, or even the first 10-50 rows to be safe. Usually its when you have a number in the first row and text later on in the same column that you get stung - as often happens with ID's that can have numeric values or text values). Then run a delete query to delete the dummy row, or import to a staging table and append to the real table without the dummy row.

text, 1, 1.31, 12/31/2011

 

[ajm]

due to excel's guess work when selecting data type, i had put several alpha numeric customer ids at the start of the table to try and force access to make that field a text field. but no luck. unless its of major interest to anyone, i managed a workaround which will do for now so am quite happy for this query to go to the keeper. many thanks Phil, Norie, Joe, Xenou

 

[Norie]

Did you try creating a blank table with fields of the correct data type(s)?

With that you wouldn't need to do anything with the data in Excel.