Convert a query to a table

[Guest]

I'm running into an issue where I have a very large table that had a field
reflecting as text, but I need to reflect as numbers for query purpose. When
I attempt to change an error is returned indicating not enough memory.

Any suggestions? I thought I could convert a query into a table and adjust,
as the table would be much smaller. However, not sure how to complete this
task. I also though of exporting the query and then importing again, however
still to large to accomplish this task. Or, is there a way to write a
formula in my query converting text to numbers.


Any thoughts.

 

[John Spencer]

You can try
Val(SomeField)

(converts strings to number values, however errors on null, always returns
zero if the string can't be a number, and if value can be interpreted as
scientific notation (1e3)it will convert that number (1000), and it will
convert "123A456" to 123.)

I would use the following if I were unsure that all the values in the string
field were number characters (- is allowed)
IIF(IsNumeric([SomeField]),Val([SomeField]),Null)

That will convert any field that is all numeric characters or that can
represent a scientific number, but will not error on nulls and will not the
leading numbers on a string to a number value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I'm running into an issue where I have a very large table that had a field
reflecting as text, but I need to reflect as numbers for query purpose. When
I attempt to change an error is returned indicating not enough memory.

Any suggestions? I thought I could convert a query into a table and adjust,
as the table would be much smaller. However, not sure how to complete this
task. I also though of exporting the query and then importing again, however
still to large to accomplish this task. Or, is there a way to write a
formula in my query converting text to numbers.

The simplest way I can think of is to create a new, empty table with the
desired field types; and make your query into an Append query, appending the
data into the new table.

John W. Vinson [MVP]