<div>
Something like...<br />
<br />
Dim sWhere as String <br />
<br />
If Nz(Me.cboJobNum, &quot;&quot;) &lt;&gt;&nbsp;&quot;&quot; then <br />
&nbsp; &nbsp;sWhere = &quot;[JobNumber]=&quot; &amp;&nbsp;Me.cboJobNum &amp;&nbsp;&quot; AND &quot;<br />
end if<br />
<br />
If Nz(Me.cboPipeOD, &quot;&quot;) &lt;&gt;&nbsp;&quot;&quot; then <br />
&nbsp; &nbsp;sWhere = &quot;[PipeOD]=&quot; &amp;&nbsp;Me.cboPipeOD &amp;&nbsp;&quot; AND &quot;<br />
end if<br />
<br />
If Nz(Me.cboPipeWall, &quot;&quot;) &lt;&gt;&nbsp;&quot;&quot; then <br />
&nbsp; &nbsp;sWhere = &quot;[PipeWall]=&quot; &amp;&nbsp;Me.cboPipeWall &amp;&nbsp;&quot; AND &quot;<br />
end if<br />
<br />
If sWhere &lt;&gt;&nbsp;&quot;&quot; then <br />
&nbsp; &nbsp; 'Select all<br />
&nbsp; &nbsp; DoCmd.OpenReport &quot;Your Report Name&quot;, acViewPreview<br />
else<br />
&nbsp; &nbsp; 'User chose something. <br />
<br />
&nbsp; &nbsp; 'Whack the final AND <br />
&nbsp; &nbsp; sWhere = left(sWhere, len(sWhere) - 5)<br />
<br />
&nbsp; &nbsp; DoCmd.OpenReport &quot;Your Report Name&quot;, acViewPreview, sWhere<br />
end if<br />

</div>


<div>
&quot;(JobNo = &quot;&nbsp;&amp; forms!formname!cboJobNum &amp;&nbsp;&quot; or &quot;&nbsp;&amp; isnull(forms!formname!cboJ<wbr />obNum) <br />
&nbsp;&amp; &quot;&nbsp;and (PipeOd = &quot;&nbsp;&amp; forms!formname!cboPipeOd &amp;&nbsp;&quot; or &quot;&nbsp;&amp; isnull(forms!formname!cboP<wbr />ipeOd) <br />
&amp;&nbsp;&quot; and (PipeWall =&quot; &amp;&nbsp; forms!formname!cboPipeWall<wbr /> &amp;&nbsp;&quot; or &quot;&nbsp;&amp; isnull(forms!formname!cboP<wbr />ipewall) <br />
<br />
Assumes all the values are numbers<br />
<br />
<br />

</div>


<div>
Or if you prefer it can be done in 1 line...<br />

<pre><code class="code-1 nolinks" id="code-122807"><span>DoCmd.OpenReport &quot;rptYourReport&quot;, , , Mid(&quot;&quot; &amp; &quot;AND (JobNum = &quot; + cboJobNum + &quot;)&quot; &amp; &quot;AND (PipeOD = &quot; + cboPipeOD + &quot;)&quot; &amp; &quot;AND (PipeWall = &quot; + cboPipeWall + &quot;)&quot;, 5)</span>
</code></pre>
</div>


<div>
If the controls are numeric values you'll need a slight tweek and a support function (provided below). &nbsp;This is because VBA will try to handel the number as a calculation instead of using it for string concatenation. &nbsp;The solution you see here works without having to check every controls value by leveraging the differences between + and &amp;&nbsp;for concatenating your WHERE clause. &nbsp;If the control is Null + will propagate the null and remove everything until the ampersand (&amp;) is encountered.<br />
<br />
DoCmd.OpenReport &quot;rptYourReport&quot;, , , Mid(&quot;&quot; &amp;&nbsp;&quot;AND (JobNum = &quot;&nbsp;+ En(cboJobNum) + &quot;)&quot; &amp;&nbsp;&quot;AND (PipeOD = &quot;&nbsp;+ En(cboPipeOD) + &quot;)&quot; &amp;&nbsp;&quot;AND (PipeWall = &quot;&nbsp;+ En(cboPipeWall) + &quot;)&quot;, 5)<br />
<br />
The Mid() function is used to trim out the &quot;AND &quot;&nbsp;that is sure to be there if one of the values were present.<br />

<pre><code class="code-1 nolinks" id="code-122815"><span>Function En(var As Variant) As Variant</span>
<span>    If Len(var &amp; &quot;&quot;) Then</span>
<span>        En = var &amp; &quot;&quot;</span>
<span>    Else</span>
<span>        En = Null</span>
<span>    End If</span>
<span>End Function</span>
</code></pre>
</div>


<div>
Rick - I am attempting to implement your solution. &nbsp;I have added the En Function as well as the Mid function to open report command. &nbsp;I am being prompted for a parameter asking for &quot;P&quot;. &nbsp;I have no idea why this would be occuring. &nbsp;I have checked all queries and have no criteria in place.<br />
<br />
&nbsp;DoCmd.OpenReport strReport, acViewPreview, , Mid(&quot;&quot; &amp;&nbsp;&quot;AND (JobNum = &quot;&nbsp;+ En(cboJobNum) + &quot;)&quot; &amp;&nbsp;&quot;AND (OD = &quot;&nbsp;+ En(cboPipeOD) + &quot;)&quot; &amp;&nbsp;&quot;AND (Wall = &quot;&nbsp;+ En(cboPipeWall) + &quot;)&quot;, 5)
</div>


<div>
Ignore the above comment. &nbsp;Our Job Numbers are in a P-##### format.
</div>


<div>
First we'll need to know whether the P has anything to do with the criteria. <br />
<br />
The code below will send the WHERE clause in in addition to the values inside each combo box to the Debug Window (Ctl+G to Open the Debug Window) allowing you to cut and past the results back here.<br />
<br />
You might also try running the report by simply double clicking it in the Reports collection or by running the following line of code....<br />
<br />
&nbsp;DoCmd.OpenReport strReport, acViewPreview<br />
<br />
Let me know what it comes up with. &nbsp;<br />
<br />
Also, just to be sure when you say you added the En Function as well as the Mid function, what do you mean by adding the Mid() function. &nbsp;The sample provided avails a new function called En() which is short for Empty String to Null, but there wasn't a Mid() function - that is internal. &nbsp;I presume you just mean you're using it as illustrated but if there's more to it that too could be the issue. &nbsp;If you've created a new function called Mid() you'll want to remove it so that it doesn't override the Mid() function provided by VBA.<br />

<pre><code class="code-1 nolinks" id="code-122873"><span>Debug.Print Mid(&quot;&quot; &amp; &quot;AND (JobNum = &quot; + En(cboJobNum) + &quot;)&quot; &amp; &quot;AND (OD = &quot; + En(cboPipeOD) + &quot;)&quot; &amp; &quot;AND (Wall = &quot; + En(cboPipeWall) + &quot;)&quot;, 5)</span>
<span>Debug.Print &quot;1: = &quot; &amp; cboJobNum</span>
<span>Debug.Print &quot;2: = &quot; &amp; cboPipeOD</span>
<span>Debug.Print &quot;3: = &quot; &amp; cboPipeWall</span>
</code></pre>
</div>


<div>
I apologize - I thought I had solved the problem, but I am still prompted to enter a value for P. &nbsp;
</div>


<div>
Regarding the functions, you are correct...I added a the En function as a new function and I added the Mid(...........) to the DoCmd.OpenReport line.<br />
<br />
I am able to open report straight from the collection.<br />
<br />
I am not having any success with the debug code...perhaps I placed in the wrong spot.<br />
<br />
As far as the criteria...as mentioned above, our Job Numbers are P - #####, so perhaps treating it as a number with the En function could be causing a problem.
</div>


<div>
Just Rem Out the DoCmd.OpenReport line and add the Debug Lines above or below it. <br />
Click the button you use to run your report then open the form in design view, open the code window and click Ctl+G. &nbsp;You should see 4 lines of information sent to that window which you can then cut and past here so we can see what is going on.
</div>


<div>
(JobNum = P-14766)AND (OD = 14)AND (Wall = 0.237)<br />
1: = P-14766<br />
2: = 14<br />
3: = 0.237
</div>


<div>
That's good. &nbsp;There are actually a couple issues. &nbsp;Code snippet below.<br />
<br />
A Trailing space was needed after the closing parentheses, (sorry - missed that), but also it would appear that JobNum is actually a Text field so it needs to be encapsulated with quotes or an apostrophe. &nbsp;Also the value of JobNum needs to be checked for any apostrophe's within it and if found they need to be doubled up, (Hence the addition of the Replace() function.<br />
<br />
Anyway, give the code below a try.<br />
<br />

<pre><code class="code-1 nolinks" id="code-122904"><span>DoCmd.OpenReport strReport, acViewPreview, , Mid(&quot;&quot; &amp; &quot;AND (JobNum = '&quot; + Replace(En(cboJobNum),&quot;'&quot;,&quot;''&quot;) + &quot;') &quot; &amp; &quot;AND (OD = &quot; + En(cboPipeOD) + &quot;) &quot; &amp; &quot;AND (Wall = &quot; + En(cboPipeWall) + &quot;) &quot;, 5)</span>
</code></pre>
</div>


<div>
Rick - You are a genius. &nbsp;The code works, but I still have one final problem...I hope it is a simple fix. &nbsp;If I only want to filter the report on one of the three fields, and I leave the other two null, I get an &quot;invalid use of null&quot; error message.<br />
<br />
(OD = 6.625)<br />
1: = <br />
2: = 6.625<br />
3: = 
</div>


<div>
Rick - You are such a genius that you actually answered my post before I even submitted it. &nbsp;I really appreciate the help.
</div>


<div>
<div class="content wysiwyg-content">
I have three combo boxes on a form to filter reports...<br />
<br />
cboJobNum<br />
cboPipeOD<br />
cboPipeWall<br />
<br />
I want all of the filters to be optional. &nbsp;How do I set up the where clause of the &nbsp;DoCmd.OpenReport to only apply the combo filters if a selection is made?
</div>
</div>


I have three combo boxes on a form to filter reports...

cboJobNum
cboPipeOD
cboPipeWall

I want all of the filters to be optional.  How do I set up the where clause of the  DoCmd.OpenReport to only apply the combo filters if a selection is made?

Open Report With Optional Filters

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.