Converting Alphanumeric characters (and spaces) to Numbers

[Anonymous_354]

In working with a database, I need to convert 39,000 text fields (i.e.: 6002.2 , 2723 , 6004.55 (Notice the spaces)(commas denote next line)) to number fields. The number of spaces is random, so I can't say "cut off the last n number of characters." There may also be characters that I'm not seeing. I'm not too keen on going through it by hand, and I have access to OpenOffice.org Calc (for those who don't know, it's basically a free version of Excel) so I can pull it into there and convert it, but it still won't get rid of the spaces though. Any ideas?

 

[boblarson]

If this is just a one time deal, you can open the table, select the column with the data and click Edit > Replace and then in the replace put

Replace: " "
with: ""

 

[Anonymous_354]

Excellent. Works great! Now will it just easily convert between a "Text" field and a "Number" field?

 

[boblarson]

If there are only numbers in the field, you should be able to convert it.

 

[Anonymous_354]

It's complaining about it being too big... Something about "Microsoft cannot change the data type. There isn't enough disk space or memory."

 

[boblarson]

Which data type are you trying to change it to?

 

[Anonymous_354]

Text into Long Integer

 

[boblarson]

Do any of the numbers have decimal places? If so, then Integer is not what you want. I would suggest Double in that instance.

Also, you might try adding a column in the table with the appropriate datatype and the using an update query update that column to the value in the other column and then running that and then delete the old column and rename the new one back to the old name.

 

[Anonymous_354]

I get the same problem with a single, double, integer, long integer, and decimal.

I'm not used to update queries. Could you explain how to use them?

 

[boblarson]

1. Go to the database window and click on QUERIES

2. Click New Query and then follow everything to create a select query for the data by selecting the table you want and the new field.

3. Then, with the query in design mode go to the menu bar QUERY > UPDATE QUERY and it will then ask you which table you want. Then just tell it in the UPDATE row (it shows up just above the criteria spot) [YourFieldName].

4. Then run it.

 

[Anonymous_354]

Well, I ended up doing the opposite of what I wanted, so backups, here I come. It seems as though it will work though. We'll find out when my backups finish backing up...

 

[gemma-the-husky]

if there is definitley nothing in there other than a number, you can safely use val in a query

ie create a new numberfield as a double
then in your query, you can just do

newnumberfield = val(oldnumberfield)

if you need, you can rename fields afterwards to get the new number field back to its old name

 

[Anonymous_354]

Yep it works. Now to make it ALL work. I have four tables I need to link together and make it work in VB.NET. I keep having troubles trying to make the relationships work in that it keeps asking me what the relationships are. When I tell it what to think, it doesn't do what I think I tell it. Very frustrating. I can get the first relationship to work in VB.NET, but after that it gets confused. So for now, I need to make it work in Access. Any thoughts?

 

[Anonymous_354]

Ah, figured it out. I needed to fill in a column. Problem is it has >2200 rows and again, I don't really want to fill it in by hand. Any suggestions?