Excel Query, Return all if parameter is blanks

**mreuveni** · 02-12-2013, 06:41 PM

So i have several criteria in my query. the criteria take parameters which are indicated using a drop down in an excess cell.
I would like it so that if the cell is blank, that the criteria not be filtered and all items are returned.

Example in my SQL command
SELECT....
FROM.. "repair_tech"."surname" = ?

The variable is the tech's surname. I want the table to display all results if no surname is specified.

thanks
Maya

**alansidman** · 02-12-2013, 07:05 PM

Your criteria should be in your WHERE clause not in your FROM clause.

Look here http://www.w3schools.com/sql/default.asp

**mreuveni** · 02-12-2013, 07:34 PM

Yes you are right, that was a typo on my part.
I meant to write my pseudo code is
SELECT....
FROM..
WHERE "repair_tech"."surname" = ?

**alansidman** · 02-12-2013, 07:49 PM

I don't use SQL in Excel (disclaimer), but in Access in my WHERE clause I would use

WHERE [repair_tech].[surname] like "*" & Criteria & "*"

the asterisks work as wildcards and if nothing is entered in the criteria, then all records are returned.