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

[Joe4]

You can use the VAL function in a Query to convert numeric entries entered as Text to Numbers, but I question this part of your question:

it contains proper numbers and also "text" numbers where the number starts with an alpha character

What do you mean it starts with an alpha character?
If you have alpha characters, like "A23", what is the rule for converting it to numbers?
Perhaps a few examples of what your entries look like would clear that up.

[Phildaburn]

Try this:

Cust#: iif([CustomerNumberTextField] like "#*",val([CustomerNumberTextField]),[CustomerNumberTextField])

Hope that helps...
Phil...

[ajm]

You can use the VAL function in a Query to convert numeric entries entered as Text to Numbers, but I question this part of your question:
What do you mean it starts with an alpha character?
If you have alpha characters, like "A23", what is the rule for converting it to numbers?
Perhaps a few examples of what your entries look like would clear that up.

thanks joe for responding. i use the term number loosely. our customer "numbers" (lets call them customer ids, for clarity's sake) used to be all numeric characters but we now have started using customer ids that start with a single alpha character followed by 5 numerals. eg A62144. so now the customer id field contains both types of customer numbers.

I might add that the error is actually at the import stage so am now trying to figure out why access 2007 won't import these new alpha numeric customer ids but will let me paste them in. that is, i can't link a table as the alpha numeric customer ids result in a Num! error, but i can copy the entire record set and paste it in as a table with customer ids appearing perfectly.

weird.

cheers, ajm

[Phildaburn]

Are you using an Import Specification during the import? If so, perhaps it's looking for a number rather than text in that field.

Phil...

[Norie]

ajm

How exactly are you importing the data?

You should have no problem importing numeric data into text fields.

If you need to do something later with numbers that are stored as text, eg calculations, then you might need to convert them to 'real' numbers.

[ajm]

phil, norie,

i have the list in an excel table and i initially tried to link the table using the Get External Data - Excel Spreadsheet wizard and then tried importing the table using the same. both attempts resulted in the alpha numeric ids coming up as #Num!. intersetingly, when previewing the data prior to linking, the alpha numeric ids are all there.

[Phildaburn]

In the attempts where the alpha numeric ids came up as #Num! did you look at the design of the table? While excel is more flexible with how data is formatted in its columns, Access is not. To attempt to experience what you're going through, I linked an excel spreadsheet to a database. I too received those #Num! errors but only in the instances where Excel had a different data type than what the table design allowed.

Try selecting the column that contains your alpha numeric account ids and format those cells as text before linking or importing into Access and see if that improves your results.

Phil...

[Norie]

When importing Access will take a guess on the data type of each field.

It bases this on the first few rows of data.

So if that results in an incorrect guess you can end up with this sort of problem.

If that is the problem there are at least 2 ways to fix it:

1 Change the data in Excel so it is definitely all the correct data type - eg if there a are numbers make sure they are stored as text

2 Import into an existing (blank?) table where the data types for each field are already set.

I'd recommend 2 because 1 might have implications on the source data.

It's also easy to implement 2, just create a new table with all the required fields.

Alsoe, if you do import to this 'intermediate' table you can append the data to another table in Access.

Then you can clear out the 'intermdiate' table ready for the next import.

[Joe4]

To follow up Norie's previous response, another option, somewhat similar to option 2 is to export the Excel file to a CSV file (or some other text file). Then you can import the text file and tell Access exactly what the format of each field is (instead of leaving it up to Access to "guess", which is what it does when you import files directly from Excel).

Imports directly from Excel are often problematic because of this "guessing..."