Well, I just about have this x-tab thing down, but I am still floundering on how to write an SQL statement to include parameters (with wildcards) in more than one field within the query. I have had success creating parameterized crosstab queries with parameterized select queries on them, but when I use the wildcard statement for either the first or the second parameter, it ignores the other parameter values.
For example, if I specify the parameters as such:
PARAMETERS [Field1_Value] Text (255), [Field2_Value] Text (255);
SELECT MyTable.Field1, MyTable.Field2
FROM MyTable
WHERE InStr([Field1_Value],[Field1])>0 OR InStr([Field1_Value],[Field1]) IS NULL AND InStr([Field2_Value],[Field2])>0 OR InStr([Field2_Value],[Field2]) IS NULL;
and if I specify Value1 for Field1_Value and hit enter (or enter a null value) for Field2_Value, it will return all values for both parameters, even though I specified the value for Field1.
Short of writing two queries to accomplish this, is there any way to accomplish it with only one query?
Your help is always appreciated.
Captain D
For example, if I specify the parameters as such:
PARAMETERS [Field1_Value] Text (255), [Field2_Value] Text (255);
SELECT MyTable.Field1, MyTable.Field2
FROM MyTable
WHERE InStr([Field1_Value],[Field1])>0 OR InStr([Field1_Value],[Field1]) IS NULL AND InStr([Field2_Value],[Field2])>0 OR InStr([Field2_Value],[Field2]) IS NULL;
and if I specify Value1 for Field1_Value and hit enter (or enter a null value) for Field2_Value, it will return all values for both parameters, even though I specified the value for Field1.
Short of writing two queries to accomplish this, is there any way to accomplish it with only one query?
Your help is always appreciated.
Captain D
