Query Criteria Problem

[Sensibilium]

I have a query for a report, and I want to set the criteria for the 'Design' column to the previously selected 'cboCategory' in 'frmProductReports'. Individually selected Categories run the report correctly, however if the user has left 'cboCategory' set at its default value of 'All Design Categories' the report runs but without any data at all. Please see below for the Immediate If condition I'm trying to set.<br><br><FONT FACE=monospace>IIf([Forms]![frmProductReports]![cboCategory]=&quot;All Design Categories&quot;, <font color=red>What do I set this to?</font> ,[Forms]![frmProductReports]![cboCategory])</font><br><br>I've tried setting the 'truepart' to &quot;&quot; and &quot;*&quot; but neither brings up any data.<br><br>Anyone know how to do this? <p>ahdkaw<br><a href=mailto:ahdkaw@sensibilium.com>ahdkaw@sensibilium.com</a><br><a href=

http://sensibilium.com/>Sensibilium</a><br>'If

you do visit my sites - be warned! You must be of a discordian nature...'

 

[kathryn]

Just off the top of my head have you tried:<br><br>IIf([Forms]![frmProductReports]![cboCategory]=&quot;All Design Categories,,[Forms]![frmProductReports]![cboCategory])<br><br>You really don't want ANY criteria if they left it blank right?&nbsp;&nbsp;This may do it.<br><br>or<br><br>IIf([Forms]![frmProductReports]![cboCategory]=&quot;All Design Categories&quot;, &quot;&quot;*&quot;&quot;,[Forms]![frmProductReports]![cboCategory])<br><br>The double double quotes might resolve into &quot;*&quot; which is what you really want.<br><br>Sorry for not testing either of the above, but I just stopped in for a minute.<br><br>Kathryn

 

[Sensibilium]

kathryn,<br><br>I've tried the &quot;&quot;*&quot;&quot; but the report won't even run like that.<br><br>I have tried :<br><FONT FACE=monospace>IIf([Forms]![frmProductReports]![cboCategory]=&quot;All Design Categories&quot;,,[Forms]![frmProductReports]![cboCategory])</font><br><br>But Access changes it to :<br><FONT FACE=monospace>IIf([Forms]![frmProductReports]![cboCategory]=&quot;All Design Categories&quot;,&quot;,[Forms]![frmProductReports]![cboCategory]&quot</font><br><br><br><br> <p>ahdkaw<br><a href=mailto:ahdkaw@sensibilium.com>ahdkaw@sensibilium.com</a><br><a href=

http://sensibilium.com/>Sensibilium</a><br>'If

you do visit my sites - be warned! You must be of a discordian nature...'

 

[kathryn]

OK, have you tried changing the logic?<br><br>IIf([Forms]![frmProductReports]![cboCategory]&lt;&gt;]=&quot;All Design Categories&quot;,[Forms]![frmProductReports]![cboCategory]&quot<br><br>Good luck!&nbsp;&nbsp;I guess the caffeine hasn't kicked in yet.<br><br>Kathryn<br><br>

 

[Sensibilium]

Tried reversing the logic, but it still comes up with a blank report! <br><br>Arrrgggh!! Why do all the books I have on Access2000 make no mention of this at all?<br><br>I definitely need some caffiene mesel'<br> <p>ahdkaw<br><a href=mailto:ahdkaw@sensibilium.com>ahdkaw@sensibilium.com</a><br><a href=

http://sensibilium.com/>Sensibilium</a><br>'If

you do visit my sites - be warned! You must be of a discordian nature...'

 

[Guest_imported]

Just for kicks!<br>IIf([Forms]![frmProductReports]![cboCategory]=&quot;All Design Categories&quot;,&quot;Like&quot;*&quot;,[Forms]![frmProductReports]![cboCategory]&quot<br><br>

 

[tmryan2]

This has worked for me before:<br><br>1. Create a non-visible field on your form and call &quot;Selectcategory&quot;.<br>2. In the Control Source of the new field enter the following:<br>IIf([cboCategory]=&quot;All Design Categories&quot;,&quot;*&quot;,[cboCategory])<br>3. In the query criteria, reference the new field using:<br>Like [Forms]![frmProductReports]![Selectcategory]<br><br>Not very elegant, but it works.

 

[elizabeth]

If I understand your question, Rochelle answered this earlier: Here's what she wrote:<br><br><i>In your query in the criteria line under the field you want to test, write:<br><br>Like IIf(Len([Forms]![Your Form Name]![Your TextBox Name])&gt;0,[Forms]![Your Form Name]![Your TextBox Name],&quot;*&quot<br><br>If the textbox on your form is empty, this field will not be limited but if the textbox on your form is filled in, it will be used to limit the query to matching entries in the table.</i>

 

[Sensibilium]

I've tried that glo4, and it won't work... trust me on that one...<br><br>tmryan2, good idea, I sort of tried that using a hidden label, but it didn't work. I will try your method though...<br><br>elizabeth, unfortunately the combo box on my form is filled with text, so yours won't work unless I alter the combo box, I'll try it if all else fails...<br><br>Thank you all... But it may not be over yet... <p>ahdkaw<br><a href=mailto:ahdkaw@sensibilium.com>ahdkaw@sensibilium.com</a><br><a href=

http://sensibilium.com/>Sensibilium</a><br>'If

you do visit my sites - be warned! You must be of a discordian nature...'

 

[elizabeth]

OK, think I understand now. How about<br><br>Like IIf([Forms]![Your Form Name]![Your TextBox Name])=&quot;All Design Categories&quot;, &quot;*&quot;,[Forms]![Your Form Name]![Your TextBox Name])<br><br>?

 

[Sensibilium]

Now my problem has switched round! The report now works, and lists all Categories, but now the individual Categories don't work. This is because Access translates it to :<br><br><FONT FACE=monospace>Like Belmont</font><br><br>So, there are no quotes on either side of the Category name...<br><br>Hmm... It just gets more complicated...&nbsp;&nbsp;( <p>ahdkaw<br><a href=mailto:ahdkaw@sensibilium.com>ahdkaw@sensibilium.com</a><br><a href=

http://sensibilium.com/>Sensibilium</a><br>'If

you do visit my sites - be warned! You must be of a discordian nature...'

 

[elizabeth]

Sorry, should've thought of that. Try this - I'm not so hot with the punctuation but I think this is how it should go...<br><br>Like IIf([Forms]![Your Form Name]![Your TextBox Name])=&quot;All Design Categories&quot;, &quot;*&quot;, &quot;'&quot; & [Forms]![Your FormName]![Your TextBox Name] & &quot;'&quot<br><br>In case this is hard to read, I'm concatenating opening and closing quotes with the filed. So there are three quote marks in a row, as the quote moark itself needs to be enclosed within quotes. Clear as mud?

 

[tmryan2]

<br>I just realize there is also another wrinkle all to the solutions suggested:<br><br>You are trying to return all records using &quot;*&quot; or &quot;Like *&quot; as the criteria. However, any variation of &quot;*&quot; will only only return non null records.&nbsp;&nbsp;If there is a record where that field happens to be empty, then it will not show up in the results.<br><br>So I think we should actually be using &quot;Like * Or Is Null&quot; as the criteria, but I'm not sure how to incorporate all the punctuations into the expression to make it work.<br><br>

 

[Sensibilium]

Elizabeth,<br><br>Sorry, but I tried your original criteria again, without changing it, and it worked! Very weird! Must have been Access playing silly buggers with me first time round.<br><br>Thanks for your solution, 'twas much appreciated )<br><br>tmryan2:<br>I understand what your trying to do, and it would be of use to others, but for me it is no good, as none of my records contain Null in the Category field. Cheers anyway ) <p>ahdkaw<br><a href=mailto:ahdkaw@sensibilium.com>ahdkaw@sensibilium.com</a><br><a href=

http://sensibilium.com/>Sensibilium</a><br>'If

you do visit my sites - be warned! You must be of a discordian nature...'