Excel MS Query - How to write parameter equals "" or show all in SQL query

Question

Just finished writing an SQL script in the MS-Query and I'm having difficulty trying to get it to work.

What I'm after is the equivalent of this SQL where clause:

AND ((examplefield = @Para) or (@Para = ''))

So if parameter = something in the field, only show that or if the parameter = blank then show all results.

So far this is what I have which works fine if I want to select a particular item, now I just need to include the additional if blank show all.

AND (`'Project Master List$'`.`Type of Work`= ?)

This unfortunately doesn't work.

AND ((`'Project Master List$'`.`Type of Work`= ?) OR (? = ""))

Any suggestions?

Are you looking for if it is (Your Value AND (blank AND NULL) Or (Your Value) AND (Blank OR NULL) OR (Your Value) AND (Only Blank, not NULL) — Elias, Sep 18 '13 at 13:50
Hi Elias, I have a drop down list which has a list of products for example and at the top I've included a blank entry which the user can select to show all the products. Looking at the top code, if the user selects a product then it will return all results that match it, if instead they select the blank then it will show all the products as that part of the statement is satisfied by blank = blank. — TJH, Sep 18 '13 at 14:13

Elias · Answer 1 · 2013-09-18T14:30:59.047

0

Try a case?

Case When ?="" THEN
  'docode'

  WHEN ?="OtherValue" THEN
  'DoCode'
  Else
  'DoCode '
  End

The IIF Example:

iif(?="", 
iif(?="OtherValue",ReturnSomethingTrue,ReturnSomethingFalse)
,ReturnSomethingTrue,ReturnsomethingFalse)

edited Sep 18 '13 at 14:30

answered Sep 18 '13 at 14:18

Elias

Hi Elias, unfortunately not, MS Query SQL scripts don't like CASE statements for some reason. – TJH Sep 18 '13 at 14:23
I forgot. Have you tried IIF statements? You could nest them. I will provide an example above. – Elias Sep 18 '13 at 14:29
I looked back at the bottom where clause in my question and realised I'd wrapped it in the wrong quotes. Thanks for the responses Elias. – TJH Sep 18 '13 at 14:48
Ha, I hate when things like that happen. Good luck with the rest of your project! – Elias Sep 18 '13 at 14:55

score 0 · Answer 2 · answered Sep 18 '13 at 14:53

I looked back at my original question and realised I wrapped the where clause in the wrong quotes. See below

Wrong statement

AND ((`'Project Master List$'`.`Type of Work`= ?) OR (? = ""))

Correct statement

AND (`'Project Master List$'`.`Type of Work`= ? OR ? = '')

Once I'd made the change right at the end of the clause it worked.

2 Answers2