ERROR!! tying to change a text value to number

[abesimpson]

I am attempting to do a calculation on a field that displays numbers as text values. The database is approx 1.2G in size and when I attempt to change the field properties of the table from text to number I get the following error message "There is not enough disk space or memory".

This seems unlikly because there are 17G of free disc space available and 1G of Ram; when the message is displayed 600Meg of Ram is shown free.

Is ther a work around to this problem or use a formula that converts text to a number within a query?

Thanks

 

[dk]

Are you trying to permanently change the field type or do some type of conversion in your query?

To convert text to a value in a query use one of the conversion functions - CCur, CLng, CDbl, CSng (see Type Conversion Functions in VBA help) or Val.

If you want to permanently change a data type then an alternative is to make a copy of your table (structure only and change the data type to what you need) and then create an append query to append data from your original table to your new table. I've ran into the problem you've described and this is one way of getting round it.

 

[abesimpson]

DK (and anyone else)

Your Visual Basic VDbl suggestion worked.

I also tryed the data append solution... works like a charm on a small dataset!!! BUT, when I try it on the real data I get a new error message "Invalid Argument"; before the append query has completed.

Any new suggestions?

Many Thanks

abe ray: