>>>Hi June,
>>>
>>>>If I've got a parameterized view where sometimes I want to use field1 as the comparison for the parameter, and sometimes I want to use field2 (with a different value for the parameter), how do I do this with MM? Do I have to create two views, identical except for the filter tab, or is there some other way? Is there a way to use views created at run time with MM?
>>>>
>>>
>>>I don't use MM, but can you create one view with filters on both fields? Then, set one of the filter variables to a wildcard (or empty string) when you are not using it. In other words:
>>>

>>>lcParameter1 = "FilterValue"
>>>lcParameter2 = ""
>>>Select * from MyTable where Field1 = ?lcParameter1 and Field2 = ?lcParameter2
>>>

>>>Swap the values for the variables when you want to filter on Field2.
>>
>>Hi Joel,
>>
>>The above select statement is dependent on SET ANSI = OFF since field2 = "" will be TRUE in that case but will be FALSE if SET ANSI = ON. One way that I do this is
>>

>>*!* select on field 1
>>lcParameter1 = "FilterValue"
>>lcParameter2 = "SOME BOGUS VALUE THAT WILL NEVER MATCH"
>>Select * from MyTable where Field1 = ?lcParameter1 OR Field2 = ?lcParameter2
>>
>>*!* select on field 2
>>lcParameter1 = "SOME BOGUS VALUE THAT WILL NEVER MATCH"
>>lcParameter2 = "FilterValue"
>>Select * from MyTable where Field1 = ?lcParameter1 OR Field2 = ?lcParameter2
>>

>
>Elmer,
>I'd prefer Joel's way. If you have ANSI OFF at all times then you could use == inline for exact matches. Having OR changes the logic and fails when both are meant to be supplied. Depending ANSI OFF is better IMHO.
>Problem arises from values that're not character. For them I change the expression from = to between like:
>
>lnMinId = iif(knownValue, knownValue, PossibleMin)
>lnMaxId = iif(knownValue, knownValue, PossibleMax)
>
>... where myID between ?m.lnMin and ?m.lnMax
>
>Cetin

Hi Cetin,

I agree that SET ANSI OFF will make Joel's select more flexible where you could use it for either field1, or field2 or (field1 and field2), when used on local views and queries. But if using the pre-VFP8 view designer where you can't edit the view definition code, there is no way to enter == unless you define the view or SQL in code. Also I'm not sure how that would work with a remote view that is designed to be used with various backend databases as the == may yield a syntax error when used on some databases. I use remote views that must work on VFP, SQL Server, or Oracle. I have been bitten more than once with views that work as desired with VFP database, but fail on SQL Server or Oracle, so I try to use very basic non-VFP specific syntax on SQL and test it on each backend. Also without SET ANSI = ON, field1 will match on FilterValue, FilterValuexxxx, etc with local tables unless you use ==.

In my testing, using remote views and SPT, querys behaves as if SET ANSI = "ON" no matter what my local setting is for ANSI, although that may be some setting of my ODBC connection that is affecting this, though I have never set it. This is why I run with SET ANSI = ON so that my local queries operate the same way as my remote views and SPT.

The original requirement was "sometimes I want to use field1 as the comparison for the parameter, and sometimes I want to use field2 (with a different value for the parameter)" which to me indicates that he wants to use either field1 OR field2 using the same p-view. My SQL while not elegant, satisfies the requirement where you can query on either field1 OR field2 that is not as dependent on Set ANSI, and works with local and remote views and SPT.