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