A97: Text conversion to Number type

[RonMcIntire]

All:

Oops! Wrong forum. Should be Access 97.

I have a table with a field of alpha numbers that I need to change to Number format. How do I do it? Any ideas?

Simply changing the field type in the table wipes out the column.

Ron McIntire

 

[lespaul]

I would first ask, why? The only time you should store numbers as numbers is if you are going to be performing calculations on them. For instance, Social Security Numbers, Zip Codes, Phone Numbers are all numeric, but you don't add any of them together. If you don't do any mathematical calculations with these numbers, don't change them.

If I decided for some reason to do it anyway, I would BACKUP the database. Then I would create a new field in the table as the datatype you need. Then do an update query setting the new field = to the old field (you may have to convert the datatype in the query to get it to work, may not). Once you check that the information in the new field is correct, remove the old field.

Not sure if you will run into any issues with queries though by changing the datatype. I know that my front end would CHOKE if I changed something from a string to a number.
Leslie

 

[RonMcIntire]

Leslie:

Thanks for responding.

I periodically download a membership database from our headquarters. I then, through a series of queries, format and use the data to generate a directory.

Up to now the members table in the downloaded file has defined the home phone field as an 11 characher number of NUMBER type. For some reason, probably an error, the phone field is now defined as a TEXT type. Thus, I must now rewrite the appropriate portions of my program to make it work.

I can and have exported the data to an Excel file, modified the data to a number then reimported it into Access. But there has got to be a better way. I thought Access had a function or feature that allowed the change. Simply changing the type wipes out the field in the table and appending the data to the appropriate table structure generates an error. I thought about using a make-table query but don't know how to change the field type in a query.

Any better ideas than what I've used?

Ron

 

[psemianonymous]

1. Make a temp field, type Number

2. UPDATE tblYOURTABLE SET tmpFIELD = CLng([PhoneNumberFIELD])
-with this, you may have to do more than just a CLng() function, especially if it has dashes ("-&quot in it

3. Rename PhoneNumberFIELD to PHFIELD2

4. Rename tmpFIELD to PhoneNumberFIELD


Unfortunately, you can't choose the data type for each field when importing from Excel. I suspect that some garbled text is in the ph# field somewhere in the spreadsheet.


As an alternate strategy, maybe have Step#1,2 be inside a temp-table that you use only for importing the Members table, and then just run an "Append" query into your REAL Members table once you have re-formatted the phone# as a number.