Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms.
Code of Conduct .
Join Stack Overflow to learn, share knowledge, and build your career.
Email Sign Up
or sign in with
convert text to number in ms access 2010 select statement
I need to run a query on a massive (650 MB) data table in MS Access 2010. All the data types for the fields are text, but some fields contain all numbers, even though the data type is text. I therefore have to convert from text to long int in order to select, for example, all the records who have a value greater than 10 in a specified field. But whenever I do this, I get a type mismatch error, presumably due to the comparison of a numerical value of a text field. Here is the SQL for the query which should select from fields that are all of the text data type.
SELECT [Organization legal name], [Number of Group Practice members], City, State FROM massivetable WHERE Clng([Number of Group Practice members])>10 AND State='CT';
I have tried to convert the datatype of the field to long integer in design view, but the operation fails with a message saying there is not enough memory to perform the operation.
Can anyone show me how to fix the code above so that it selects all the records while values greater than 10, without throwing a type conversion error?
- You may have to export all the data, get the database setup properly (either edit the existing database after removing all the previously exported data OR Create a NEW copy of the database) then import the data into the database to get everything back in place. I had to do it once before on a project and it saved the day. size of your database is larger than what I was working with though. good luck.
Nov 8 ’13 at 2:56
- @tamak Do you have any solution to my next question? I am trying to shrink the table with a WHERE clause and INSERT INTO. Here is the link to the question: stackoverflow.com/questions/19850911/…
Nov 8 ’13 at 3:09
add a comment |
Use IsNumeric function:
SELECT [Organization legal name], [Number of Group Practice members], City, State FROM massivetable WHERE Iif(IsNumeric([Number of Group Practice members]), Clng([Number of Group Practice members]), 0) >10 AND State='CT';
- +1 and credit for the answer. thank you.
Nov 8 ’13 at 2:58
- Do you have any solution to my next question? You can read it at this link: stackoverflow.com/questions/19850911/…
Nov 8 ’13 at 3:08
add a comment |
Not the answer you’re looking for? Browse other questions tagged sql ms-access or ask your own question .
4 years, 9 months ago
4 years, 9 months ago
insert into query without listing destination fields explicitly
A select query selecting a select statement
‘IF’ in ‘SELECT’ statement – choose output value based on column values
Convert String to Date in MS Access Query
Access 2010 SELECT string
Creating a Temporary Incrementing Number in an MS Access Query
Access SQL – Convert Text to Number
Why does search condition fail on MS Access number field that is indexed?
Select a record with the largest sum of fields (Access 2010)
How do I fix this SQL SELECT statement for MS Access
Access: Combining multiple rows into one, but show text
Hot Network Questions
Is this meme about the former prime minister of India true?
How can I explain why my mechs don’t sink into the ground?
Standard behavior for direct initialization of unsigned short
Is a pig-mounted cavalry possible?
Why does Samsung include useless capacitors?
How can I adjust playing two keys an octave apart to something that I can reach with a spread of 8 keys?
How to Write an Eldritch Abomination?
Why didn’t the Spacecraft used for the Apollo 11 mission melt in the Earth’s Atmosphere?
Can two universities have the same name?
Why do ex-government employees have security clearances?
Kid throwing ice cream cone back to the vendor
How do I draw a box with holes?
Can I receive a package in Zurich airport while on transit?
Codegolf Rainbow : Sorting Colors with Reflection
What is the ubuntu-minimal package and do I need it?
Why is peer review so random?
Completion of measure spaces – uniqueness
Why does there seem to be a lack of conservative comedy and comedy-news compared to liberal?
Who was the first female ruler to be given the suffix, "the great"?
S and P 500 Prediction
Who’s not welcome here?
Can I attack with the same weapon for normal attack and Two-Weapon Fighting in the same turn?
Is preparing for a lawsuit a valid reason to defer an exam?
more hot questions
- What is Microsoft Access
- Object Tips
- Quick Tips
- Version Tips
- Access 97
- Access 2007-2010
- Tips via email
- Share your secrets
- Learn VBA
- Access to SQL
- Whats New!