CODEWITHSUNDEEP
sqlms-access
CodeMed
CodeMed

Reputation: 9191

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?

Upvotes: 0

Views: 23349

Answers (1)

cha
cha

Reputation: 10411

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';

Upvotes: 3

Related Questions