Show by parameter or is null

Tinwolf

This has got to be easy to solve but I can`t seem to do it.

I`m trying to do a report which allows the user to search by 3 parameters which aren`t required to be entered but I want the data to be shown if one of the parameters is null in this case the abcclassification.
If I remove the abc parameter then null abcclassifications are shown but it seems if a parameter is included it must have a value to return data.

select
allpartmaster.partnum,
allpartmaster.partdesc,
allpartmaster.prodgroup,
stockedparts.abcclassification

from allpartmaster
left join stockedparts
on allpartmaster.partnum = stockedparts.partid

where allpartmaster.prodgroup like ?
and allpartmaster.partnum like ?
and stockedparts.abcclassification like ?

What do I need to do to allow null parameters to be returned?

Hans_vd

Hi,<br />
<br />
You can rewrite your query like this:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>select
allpartmaster.partnum,
allpartmaster.partdesc,
allpartmaster.prodgroup,
stockedparts.abcclassification

from allpartmaster
left join stockedparts
on allpartmaster.partnum = stockedparts.partid

where allpartmaster.prodgroup like ?
and allpartmaster.partnum like ?
and ( stockedparts.abcclassification like ? OR ? is null )
</pre>
<br />
You will have to bind the same report parameter to the dataset twice, as for each "?" in the query there must be dataset parameter.<br />
<br />
Hope this helps<br />
Hans