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