Change field from TEXT to NUMBER

[Jeff]

I have a table with 298,000 recors one of the fields labeled sales is now a
text fieldthat I need changed to a number field for query purposes. the
fieldlooks like the following $5,000,000. I would also need to remove the
dollar signs. Every time I have tried to change the field to a number field
under DESIGN it gives me an error message not enough disk space or memory.
I have over 75G od disk and 2.4G ram

 

[GBA]

one could do text manipulations to get rid of the $ and , symbols and then
cast it to number......but before doing that dance I would experiment and
simply try exporting that table to excel. there is a chance that excel will
default that column to currency....

then you could import it back.....

 

[John Spencer MVP]

Try adding a new column with the proper data type - Currency?

Then try an update query

UPDATE YourTableName
SET YourTableName.NewField = CCur([YourTableName].[ExistingTextField])
WHERE IsNumeric([YourTableName].[ExistingTextField])


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have a table with 298,000 recors one of the fields labeled sales is now a
text fieldthat I need changed to a number field for query purposes. the
fieldlooks like the following $5,000,000. I would also need to remove the
dollar signs. Every time I have tried to change the field to a number field
under DESIGN it gives me an error message not enough disk space or memory.
I have over 75G od disk and 2.4G ram

What I've found to be the problem in cases like this is that Access tries to
keep the entire table in memory - *twice* - as it's changing the datatype.

A solution is to create a new, empty table by copying the current table,
design mode only. Change the datatype of this field, and then run an Append
query to migrate the data into the new table. Check to see that all the data
moved (you may need to do some tweaking to get rid of the currency sign) and
then drop all relationships to the old table, rename the old table to
tablename_OLD, rename the new table to the original name, and reestablish
relationships.

I'd suggest cleaning up the data first - do a few searches, perhaps such as
searching for records where IsNumeric([fieldname]) is False, or wildcard
searches for

LIKE "*[!$,0-9]*"

to find records containing anything OTHER than dollar sign, comma and digit.