title: Filtering with Listboxes in Access 2010
body: <p>Setup: Microsoft Access 2010 (at the moment nothing else is possible) with a projects table containing <code>projectName</code> (text) and columns for <code>statusID</code> (integer) and <code>generationID</code> (integer) amongst others. The integer columns refer to their respective tables where I can retrieve their short and long names.</p>
  
  <p>Goal:</p>
  
  <p>A form where I/the user can select the name of a certain generation in a listbox and the form will then present all the projects with the selected generation. In real life this is a bit more complex as there are a few more columns of course. ;-)</p>
  
  <p>After filtering the values I need to create reports which should then be exported as PDF</p>
  
  <p>The goal (Form, Reports everything) was achieved with LibreOffice Base using subforms and a filter table. I have working code for the SQL query which did everything. But ooBase it out of the question.</p>
  
  <pre><code>SELECT "ftse"."stateTextShort", "ftgn"."genName", IFNULL( "p"."projGen", 9999 ) AS "pGn",
  FROM "01_Projects" AS "p", "filterTable" AS "fT"
  LEFT OUTER JOIN "10_State" AS "s" ON "p"."projState" = "s"."stateID"
  LEFT OUTER JOIN "20_Generation" AS "g" ON "p"."projGen" = "g"."genID"
  LEFT OUTER JOIN "10_State" AS "ftse" ON "fT"."filterState" = "ftse"."stateID"
  LEFT OUTER JOIN "20_Generation" AS "ftgn" ON "fT"."filterGen" = "ftgn"."genID"
  WHERE "pIx" = IFNULL( ( SELECT "filterProjIndex" FROM "filterTable" ), "pIx" )
  AND "pSe" = IFNULL( ( SELECT "filterState" FROM "filterTable" ), "pSe" )
  ORDER BY "p"."projName" ASC
  </code></pre>
  
  <p>I hope I didn’t generate complete garbage with that SQL statement as it was a whole lot longer and included lots more columns.</p>
  
  <p>Question:</p>
  
  <ul>
  <li>I cannot use <code>IFNULL</code> or <code>COALESCE</code> in my version of Access. How do I ignore filters with no values?</li>
  <li>Is the approach using the filter column the right one?</li>
  <li>How do I realize the query above in Access. I generated the following: <strong>SOLVED</strong></li>
  </ul>
  
  <p>[break for formatting. I'm a newb and didn't know better]</p>
  
  <pre><code>SELECT ps.prjNameIntern, ss.stateShort, gn.genShort
  FROM 001_projects AS ps
  LEFT JOIN 010_status AS ss ON ps.prjStateID = ss.stateID
  LEFT JOIN 020_generation AS gn ON ps.prjGenID = gn.genID
  WHERE ps.prjStateID=(SELECT [ft].[fltrState] FROM [999_filterTable] AS ft)
  </code></pre>
  
  <p>But with the two JOIN statements it doesn’t work. Once I leave out the second or first one and the corresponding column it works fine. <strong>SOLVED</strong></p>
  
  <p>Lots of questionmarks …</p>
  
  <p>UPDATE:</p>
  
  <p>Ok, I now got the multiple  <code>JOIN</code> statements working. I stumbled across the differences of the draftview and the SQL-View and didn't understand Access's style of parenthesis.</p>
  
  <p>The question that still remains is how to solve the absence of <code>IFNULL</code> and <code>COALESCE</code> directly in the SQL without the use of VBA.</p>
  
  <p>I tried something like the following bit of code to test the use of <code>CASE</code> or <code>IIF</code></p>
  
  <pre><code>SELECT fr.fltrState, fr.fltrPltf,
  CASE WHEN fr.fltrPltf = 0 THEN 9999 ELSE fr.fltrPltf END
  FROM 999_filterTable AS fr
  </code></pre>
  
  <p>But I just gives me: <code>Syntaxerror (missing operator) in statement...</code></p>
  
  <p>I now have</p>
  
  <pre><code>SELECT
  IIF(fr.fltrState=0, 9999, fr.fltrState) AS IIFState,
  IIF(fr.fltrGen=NULL, 9999, fr.fltrGen)  AS IIFGen
  FROM 999_filterTable AS fr
  </code></pre>
  
  <p>which returns the value of <code>fltrState</code> but it doesn't return 9999 in case the column is empty.</p>