StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMS Access VBA DoCmd.ApplyFilter IsNull() evaluating false
text
Body
copied!<p>I'm writing some VBA to create a search page in my MS Acccess database and running into some trouble with <code>DoCmd.ApplyFilter</code> in the <code>Search_Click()</code> sub.</p> <p><img src="https://i.stack.imgur.com/hZD9f.png" alt="enter image description here"> My code looks like this</p> <pre><code>Private Sub Search_Click() DoCmd.ApplyFilter "", _ "([site] = [Forms]![SWP Search]![txtSite] " & _ " Or IsNull([Forms]![SWP Search]![txtSite])) " & _ "AND " & _ "([asset] = [Forms]![SWP Search]![txtAsset] " & _ " Or IsNull([Forms]![SWP Search]![txtAsset]))" End Sub </code></pre> <p>Or in pseudo code...</p> <pre><code> Shows results where true... ([site column] = txtbox1 OR isnull(txtbox1)) AND ([asset col ] = txtbox2 OR isnull(txtbox2)) </code></pre> <p>Obviously, the desired functionality is as follows...</p> <ol> <li>Site chosen, Asset blank -> Filter on site only</li> <li>Site chosen, Asset chosen -> Filter on both</li> <li>Site blank, Asset blank -> Return all rows</li> <li>Site blank, Asset chosen -> Filter on asset only</li> </ol> <p>But what's actually happening is...</p> <ol> <li>Site chosen, Asset blank -> works</li> <li>Site chosen, Asset chosen -> works</li> <li>Site blank, Asset blank -> <em>no rows returned</em></li> <li>Site blank, Asset chosen -> <em>no rows returned</em></li> </ol> <p>So it seems like when Site is blank, IsNull() isn't evaluating true and so the first part of the filter is FALSE and the thing just quits there and then.</p> <p>Any idea why? </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload