In a query only filter by a variable if it has a value

[Adam Thwaites]

I have a query looking at 3 columns. I have three global variables which can
contain values to look up in any of the three columns. The variables can also
be empty. I'm having a lot of trouble getting the query to work properly. Any
ideas?

Example:
Column1 - Coulmn2 - Column3
Red - Yellow - Pink
Red - Yellow - Purple
Red - Orange - Yellow
Red - Blue - Yellow
Red - Red - Green
Blue - Green - Yellow

Ideally I could seach for 1, 2 or 3 colours, searching for Red and Yellow
would return the first 4 lines.

 

[a a r o n . k e m p f]

I'd start with 'normalize your data properly'.

 

[Ken Sheridan]

Adam:

Aaron may or may not have a point. Your dummy data appears to have three
columns for values of the same attribute, which is verboten, but it may
simply appear that way because it is just simplified dummy data.

Variables are not exposed to queries so you'd need to wrap each one in a
function in a standard module:

Function GetVariable1()

GetVariable1 = variable1

End Function

Function GetVariable2()

GetVariable2 = variable2

End Function

Function GetVariable3()

GetVariable3 = variable3

End Function

The variables must be of Variant data type of course in order that they can
be Null.

But why use variables at all rather than parameters, e.g. references to
three text box controls on a form. In which case you'd reference the
parameters instead of calling the functions.

The Query would then be:

SELECT *
FROM YourTable
WHERE (Column1 = Forms!YourForm!txtOne
OR Forms!YourForm!txtOne IS NULL)
AND (Column2 = Forms!YourForm!txtTwo
OR Forms!YourForm!txtTwo IS NULL)
AND (Column3 = Forms!YourForm!txtThree
OR Forms!YourForm!txtThree IS NULL);

Ken Sheridan
Stafford, England