>Hi Sergey,
>
>Thanks for the input, and that's exactly how I've been handling it. (I have a routine that takes the user's request and builds the Select statement dynamically). However, I can't use that syntax when the user wants all values less than "H", for example.
>
>For a temp fix, I've created a routine that looks at the operator (less than, greater than, or equal to) that the client is using and also looks at the value that they want to use ("2", "A", etc). Then, based on the results of the routine, I'm building the Select statement. For example, if they are comparing strictly to a number (i.e. "2","7", etc) I can use the VAL() function. If they are comparing to a letter (i.e. "A", "B", etc), I handle it differently.
>
>It seems to be working, but I haven't tested it completely. My long term solution is to get the client to allow me to create an all numeric field (and update the legacy data, etc).
You can build your own cursor with the values that these codes translate into... something like
create cursor crsTran (cValue c(2), nValue i)
insert into crstran values ("1",1)
insert into crstran values ("2",2)
insert into crstran values ("3",3)
...
insert into crstran values ("50",50)
insert into crstran values ("A",-1)
insert into crstran values ("B",-2)
... *** if I got the values right
select * from theirtable tt ;
join crsTran ct on tt.field=ct.cvalue;
where ct.nvalue<lnParameter
You can also tweak the character value they search by and replace it with a corresponding nvalue from your cursor, i.e. if they're searching based on "B", you actually search based on -2 etc. IOW, your SQL statement speaks numbers.