Microsoft Access can't change Data Type 1

[innovative1234]

Hi,

I am trying to change the Data Type from Text to Number- Double for a particular column in the Access Database.
This Table contains thousands of records.

While doing so I get the following Message:

Microsoft Access can't change the data type.
There isn't enough disk space or memory.


Well I have about 22Gb of free space on my hard drive and my RAM is 256MB.

Is there any workaround for this issue?

TIA


- Amit

 

[PHV]

Obviously in the table all the occurrence of this column value are numeric ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Amit

Changing a field in a table after there is a lot of data is not something to take lightly.

When Access attempts this, it has to create a "backout" process in case the change fails. It then goes through and attempts to convert each record.

Another approach would be to create a new field, your numeric field, and then transfer the data from the text field to the numeric field using an SQL UPDATE statment.

Syntax would be something like...[tt]
UPDATE YourTable SET YourNumericField = CSng(YourTextField);
[/tt]

Here is a cause for concern. Of the thousands of records, the text field may contain a text entry instead of a numeric psuedo text. It is fairly safe to change a numeric field to a text field since a text field can accept more than one data type. Going the other way - text to numeric is more problematic.

This leads me to the syntax
CSng(YourTextField)

CSng converts the text field to a single percision number. Text entries that the systen can not convert generate an error and are not converted.

To wrap up, you can then go and delete the text field from the table design after you are satisfied with the results.

One last thing. Probably want to run the Database utilities to compact and repair to free up space.

Richard

 

[innovative1234]

Thanks Richard.
Your info is helpful.

Well I am pretty sure that all the value in the text field is numeric psuedo text so converting to NUMBER should not be a problem.

However I want the NUMBER to be of format DOUBLE as per Access format.

My text numeric is 20-digit long!
So will CSng still work?

Regarding Compacting - I did try that already but still get the same message :-(




- Amit

 

[willir]

Twenty digits long!!! Not many analytical numbers have to be that long. Usually, this is the type of number is the credit card (? credit card + expiration date ?)

Okay, Single (and double percision) will not work - Single will hold the number but will use exponents and drop insignificant digits - not what you want.

But Decimal does work...[tt]
UPDATE YourTable SET YourNumericField = CDec(YourTextField);[/tt]

Here is a snap shot of the immediate window / debugger in Access...

strtest ="123456789012345678901"

dbltest = cdbl(strtest)
1.23456789012346E+20  
?format(dbltest, "0.00")
123456789012346000000.00
'lost six significant digits

dectest=cdec(strtest)
?dectest
 123456789012345678901 
'works

(Note: "?" is the "print" command within the immediate window)

Try creating your new field as a decimal number with a percision of 20, 0 scale, and 0 decimal places.

...Moving on
Compact and repair your database AFTER you have completed the conversion and deleted the old text / number field. When you delete / change data, Access basically deletes the "pointers" the data, but the data actually will still reside in the database. This space is freed up after compacting the database.

Richard