IIF statements and Is Null expressions 1

[maxster]

Hi,

I'm trying to solve a problem by using IIF and IsNull. I have a simple database table with 3 fields (Col1, Col2, COl3). I have a query which for each field has a creteria expression:-

IIf(IsNull([colvar1]),Is Null Or Is Not Null,[colvar1])
IIf(IsNull([colvar2]),Is Null Or Is Not Null,[colvar2])
IIf(IsNull([colvar3]),Is Null Or Is Not Null,[colvar3])

Essentially, there are 3 parameter values (colvar1, colvar2 and colvar3) which I'm using to test and depending on if the parameter is null or not, I want it to use either the parameter value entered (if not null) or the Is Null or Is Not Null expression if it is (Null).

The main goal is to allow a multi-field search across the database table (wehich is as follows):-

Col1 Col2 Col3
jay myvalue2 myvalue3
jay hey
jay
jay hey there
jay hey random
jay

For example, if I specified that colvar1 is "jay" and colvar2 is "hey" and that colvar3 is null, then I want the query to return back all rows which match this. However in access when I attempt to run the query, I get back that the query is too complex.

If anyone can advise on what I'm doing wrong or if there is a better solution, then it would be much appreciated.

Thanks in advance.

 

[BrenoAguiar]

Can you post the SQL of the one that you are trying to run? Or Is that the one :
IIf(IsNull([colvar1]),Is Null Or Is Not Null,[colvar1])
IIf(IsNull([colvar2]),Is Null Or Is Not Null,[colvar2])
IIf(IsNull([colvar3]),Is Null Or Is Not Null,[colvar3])
??

 

[maxster]

SELECT Table1.Col1, Table1.Col2, Table1.Col3
FROM Table1
WHERE (((Table1.Col1)=[colvar1]) AND ((Table1.Col2)=[colvar2]) AND ((Table1.Col3)=IIf(IsNull([colvar3]),([col3] Is Not Null),[colvar3])));

 

[maxster]

Apologies - the above SQL statement is wrong - the actual statement I'm attempting to use is:-

SELECT Table1.Col1, Table1.Col2, Table1.Col3
FROM Table1
WHERE (((Table1.Col1)=IIf(IsNull([colvar1]),((Table1.Col1) Is Null Or (Table1.Col1) Is Not Null),[colvar1])) AND ((Table1.Col2)=IIf(IsNull([colvar2]),((Table1.Col2) Is Null Or (Table1.Col2) Is Not Null),[colvar2])) AND ((Table1.Col3)=IIf(IsNull([colvar3]),((Table1.Col3) Is Null Or (Table1.Col3) Is Not Null),[colvar3])));

I've tried to run this in Access but I get the "This expression is typed incorrectly, or it is too complex to be evaluated....etc" message

 

[PHV]

You may try this clause:
WHERE (Table1.Col1=[colvar1] Or IsNull([colvar1]))
AND (Table1.Col2=[colvar2] Or IsNull([colvar2]))
AND (Table1.Col3=[colvar3] Or IsNull([colvar3]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maxster]

That works fine and is excactly what I intedned for the functionality, many thanks for the help - it's much appreciated.