We help IT Professionals succeed at work.

Copy data in a text field to a numeric field

valmatic
valmatic asked
on
Hi.  I created a table with a text field by mistake.  Only numeric data has been entered in this field but formatted for text.  Worked great until I need to link it to a table with a numeric key.  Easy fix is to create a numeric field and copy text data into the new field in each record in numeric format since there are now thousands of records.

Is there some way to convert text data to numeric and import into that new field?  Any suggestions would be great as I don't want to manually tweak each record.

thanks
Comment
Watch Question

Make a copy of the table as backup table just in case.

You might just simply change the Field Type from Text to Number in Table Design.
If there are any text charachters used, that will be lost, but that was a wrong entry anyway and needs to be corrected.

Hope this helps,
Daniel
CERTIFIED EXPERT
Top Expert 2016
Commented:
you can run an update query to place the values of the text field to the number field

update table
set [numberField]=clng([textfield])
Mike EghtebasDatabase and Application Developer
Commented:
Star a new query:

1. Create/Design Query  to get "Show Table"
2. Select your table and click on "Add"
3. Double-click on the field which is numeric to add to QBE.
4. From "Query Type", select "Update"
5. In the QBE, in cell "Update to:", under your field enter clng([MyTable].[textfield]) you are provided.

Note 1: Change MyTable to the table name you have.

6. Run the query, it will prompt you the action it is taking, accept it and all text field will appear in the new field as number.

Note 2: Here the assumption is there is no decimal involved. If the numbers involves decimal format then, you need to replace it with:   CDbl([MyTable].[textfield])


Commented:
If by "link' you mean 'JOIN' you should be able to join the two tables by using a conversion function in the ON criteria

Select <blah>, <blah> FROM tblNumbers INNER JOIN tblText on tblNumbers.fldNumber = CLng(tblText.fldText);

Author

Commented:
Thanks all for the help.
Dan, I tried changing the field type before and I was not able.  Tried it again and it worked.  Very strange.  Not the way I wanted to go since it left to much backend work on my queries/forms though.

Otherwise I took pieces of each post and made it work the way I needed it too so I'm splitting the points.  Hope noone minds.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.