Convert a query to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
..
 
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.
Click to expand...


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]
 
Back
Top