I just tried:

select * from occ_main where cast(number as numeric(10,0)) > 1000 and cast((number) as numeric(10,0)) < 3000

and received the same error:

Error converting data type varchar to numeric. The backend is either SQL Server 2000 or SQL Server 2005 (in my case I'm working with SQLServr 2005). The field is char(8). In most cases the value will be something like:

1001
100
310

but there may be something like:

300A
3505A
101B

etc.

It appears to pull records fine until it hits one that has a character in the field like the examples: 300A

>>I need to pull a range of records based on the street number. The number field is character. The problem occurs when a letter was entered in the number field as well as in 300A, 3505B, etc. Both of the examples below return the error:
>>
>>Conversion failed when converting the varchar value '3505A ' to data type int.
>>or:
>>Error converting data type varchar to numeric.
>>
>>

>>select * from omain where 1100 < number and 3000 > number
>>select * from omain where cast((number) as decimal(10,0)) > 1000 and cast((number) as decimal(10,0)) < 3000
>>

>>
>>Recommendations?
>
>vfp read decimal(10,0) like d(10,0) and d is date.
>
>uses:
>

>select * from omain where cast(number as numeric(10,0)) > 1000 and cast((number) as numeric(10,0)) < 3000
>
>or cast(number as integer) > 1000
>