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).
Thanks for everyone's assistance.
>As long as you're not interested in negative values (A-Z)
Select * from mytable where VAL(myfield ) < 2
>
>>Here's an unusual question (or at least this seems unusual to me):
>>
>>I have a VFP table that contains (among other things) a character field containing the values 0-50 as well as the values A-Z.
>>
>>In this client's data, A-Z represents quantifiable values less than zero, even though this field is a character field in the table.
>>
>>Assuming the table name is "mytable" and the character field I am referring to is named "myfield", If I wanted to select all records where myfield contains a "value" less than "D" for example, how would I do so? I can't change the structure of the table, etc (tons of legacy data) and if I run a select statement such as:
>>
>>
Select * from mytable where myfield < "2">>
>>then I obviously get records back where myfield = "12", "19", etc (since the data is stored as a character, "12" is seen as less than "2", etc). Using VAL() on the field in advance doesn't work as part of the SQL-Select statement.
>>
>>Any suggestions on a quick solution? My hands are tied on how the data in this field is stored.
>>
>>Thanks,
