CODEWITHSUNDEEP
sqlms-accessfilter
NOOR MOHAMED
NOOR MOHAMED

Reputation: 55

SQL statement for value from multiple field

Currently I'm working on the project that has filtering function. My project as a few field that user can choose. Ex:Status,Created,Release,Date

Situation:- The user can filter the report based on all the field or any combination from the mentioned field.

If I choose the AND condition,then I need to supply all the condition that required. If I choose OR condition, it will give some wrong output if there is more than one field is chosed.

Any suggestion are really appreciated.

Upvotes: 0

Views: 34

Answers (2)

Bill Gregg
Bill Gregg

Reputation: 7147

You can test which values are passed and which ones aren't. Assuming that values not passed in will be NULL, do this:

Select .....
from ....
where (Status = @Status or @Status is null)
and (Created=@Created or @Created is null)
and (Release = @Release or @Release is null)
and (Date = @Date or @Date is null)

Upvotes: 1

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

Depending on the presence of any of the parameters that the user can possibly specify, you can check and concatenate additional 'where condition' inside your function and run the select on the latest step. So, the initial select would looke like:

         select ....
         where 1=1

And after that by checking the parameters passed, you can add: sql:=sql||' and .....'

Upvotes: 0

Related Questions