Avatar of melikins
Flag for United States of America

asked on 

Return All Values if Filter Item is Left Blank

I would like to update a Crystal report with the below subselect.  I have added the following line of code 'and WL.scode = '#sCode#'' to the query below.  When a user enters a value for wl.scode (on the report filter), the code works properly.  However, when a user does not enter a value for wl.scode (on the report filter), the report lists nothing (rather than listing ALL wl.scodes that apply).  How do I write the code to include ALL wl.scodes if a users leaves the wl.scode field blank on the report filter?  Please feel free to ask additional questions if I am not explaining myself clearly (I openly admit to being a self-taught SQL novice)!  Thanks in advance for any and all suggestions!  

select CAST(WLG.iBedroomSize as char(1)) + ' Bedroom' as wlglist,
  WL.sDesc            WLsDesc,
  WLGD.hWlApplic      WLGDhWlApplic,
  WLGD.dtApplied      WLGDdtApplied,
  WLGD.iWLPosition      WLGDiWLPosition

from WlGenDet WLGD, WlGen WLG, WlWaitList WL
where WLGD.hGen = WLG.hMy
  and WL.hProperty = #hProp#
  and WL.scode = '#sCode#'
  and WLG.hWaitList  = WL.hMy
  and WLG.bListArchived = 0
Microsoft SQL Server

Avatar of undefined
Last Comment
Sharath S

8/22/2022 - Mon