Dynamic "Where Clause" in Access Query ( not in VBA)

[MSK]

Hi all,

In MS Access 2003 + ADO + VBA, I have a query to search all my records
based on the parameters ..something like the following

// select * from tableX where reporteddate=A and assingeddate=B and
fixeddate=C //

A,B,C - are parameters

I want to have a query like the following in Query (not in VBA)

if a <> "" then whereclause = "reporteddate=A and"
if b <> "" then whereclause = whereclause & "assingeddate=B and "
if c <> "" then whereclause = whereclause & "fixeddate=C"

// select * from tableX & whereclause //

Something I want to achive like SQL server in Access 2003. Is it
possible, If yes, Kindly give me some clue.


Many thanks
MSK

 

[david epsom dot com dot au]

Add columns

expr1: [a] <> ""
expr2: <> ""

You can add criteria to these columns:
True,False


You can create criteria on other columns to go
with these True and False conditions.

Then just add more lines vertically to the criteria in the query design
view.

So you can have one set of criteria for True,True,True,
another for True,True,False,
another for True,False,True,

etc.