I've recently started to try and transition a multitude of complex Access databases to an SQL backend due to performance issues. It's my mission to fully utilize a "thin client" approach. In doing so, I'm running into a wall.
As a little background, i've built a table that i'll use to send all of my settings to that views will pull from for filtering with the intent of having a final data set sent back to Access that won't require additional querying. This table is "tblUsers_Settings".
In the Access front end, I have multiple combo boxes that when selected, are used to filter. If they are left blank, I want all records returned. The following filter works as long as the 'rPROID' has a value. If it has no value, I get no results, whereas I need to get all results if 'rPOID' has no value (basically no filter criteria was selected). I've temporarily put in the value 66 as a place holder. I've tried using '%', like '%', and a few other misc. attempts with no results.
Any suggestions on what the 66 should be replaced with to achieve the desired results? Is there a method better than CASE that is more appropriate?
= CASE WHEN (SELECT uValue FROM dbo.tblUsers_Settings AS tblUsers_Settings_2 WHERE (uUser = USER_NAME()) AND (uSetting = 'rPROID')) = '' THEN 66 ELSE (SELECT uValue FROM dbo.tblUsers_Settings AS tblUsers_Settings_2 WHERE (uUser = USER_NAME()) AND (uSetting = 'rPROID')) END