Change a field from text to number

  • Thread starter Thread starter Jimmy
  • Start date Start date
J

Jimmy

Hello I have a database with 1.300.000 records. My problem is that there is
a field in which numbers are entered, but it is a text field. I want to
change it to a number field but I can't since I get the message that there
isn't enough memory to make the change. Can someone help me because I
desporately need to make that field a number field.
Thank You
Jimmy
 
Add a new column to the table to hold the numeric value.
Create an update query that takes the value from the text field and updates
the numeric fie.d
If you still can't get this done in a single step, change the update query
to select the top 100000 rows where the numeric field is null. Then just run
this query as many times as it takes to update all the rows.
Once the values have been transferred, delete the text column.
 
Hello I have a database with 1.300.000 records. My problem is that there is
a field in which numbers are entered, but it is a text field. I want to
change it to a number field but I can't since I get the message that there
isn't enough memory to make the change. Can someone help me because I
desporately need to make that field a number field.
Thank You
Jimmy
Click to expand...

Pat's suggestion is one way to do this; with the size of your table,
though, I'd recommend another approach.

First, of course, CHECK YOUR BACKUPS. <g>

Create a new, empty database. Import this table, *design mode only*.
Change the datatype of the field (on the empty table) to Number. Be
sure to remove Microsoft's vexing default 0 setting for the Default
property of the field!

Use File... Get External Data... Link to connect to the existing
database. Import any "lookup" tables which may be required to allow
records to be added (if you have a field in your main table which has
referential integrity to such a table). Then run an Append query from
the linked table into the new table.

Finally, do the same gambit - new table, change the datatype, import
the data - on all tables which are dependent upon this main table.

Check your work thoroughly, test your forms & reports, etc.


John W. Vinson[MVP]
 
Hi Jimmy,

Try it this way:

1) Compact your database.

2) Open the table in design view and add a new number field of the
appropriate time. Save and close the table.

3) Create an update query that updates the new field with the value of
the text field. You may need to use a type conversion function, e.g. if
the text field is called XXX you might put one of these

CLng([XXX])

CDbl([XXX])

in the Update To: cell of the query design grid.

4) Run the query.

5) Check that the values have been correctly transferred to the new
field. If so, open the table in design view aqgain and delete the old
field. If you want, rename the new field.

6) Compact the database again.
 
Back
Top