Combo Boxes selecting query criteria for reports 2

[EuanPirie]

I have a form that controls which data is selected in a query. This works fine, as long as the user enters data in all fields. What I want to do is allow the user to enter data in some, not all of the combo boxes, and get data out. Is there a way using wildcards? What happens is that the query runs using a blank field as a search option, and as such does not return any data! Can I use a default setting to set it to wildcard and select all data?<br>
<br>
Pointers, suggestions, comments. I may be being very stupid, but I cannot find any details on this anywhere!<br>
<br>
Thanks.

 

[elizabeth]

You can set the query parameters in code. There is something similar in the Solutions database, the OnOpen event of the Employee Sales Report. Looks sort of like this, and you could add a test to see if a parm were present (form's field filled in) before you specified it in the query.<br>
<br>
Set dbsReport = CurrentDb<br>
Set frm = Forms!frmPrompt<br>
Set qdf = dbsReport.QueryDefs(&quot;qry4Report&quot<br>
qdf.Parameters![Forms!frmPrompt!BeginDate] = Forms!frmPrompt!BeginDate<br>
qdf.Parameters![Forms!frmPrompt!EndDate] = Forms!frmPrompt!EndDate<br>
Set rstReport = qdf.OpenRecordset()<br>
<br>
If you haven't done this before, note you also have to specify the parm TYPE in the query's property sheet.

 

[EuanPirie]

Would I be right in thinking that to check if a parameter is present you need to run a if? along the lines of<br>
if begindate = null then ignore?<br>
<br>
Thanks,<br>
<br>
Euan.

 

[elizabeth]

Should've looked before I leapt here! Gave you the wrong example. That was for creating a report, you're trying to put data into the form. Probably a better approach is to build the underlying query (strSQL) with your if statements, then refresh the form. I'm out of my comfort zone now, but the following may be of some help... <br>
<br>
strSQL = &quot;SELECT (blah blah blah...) WHERE &quot;<br>
If Not IsNull([FormFieldField1]) Then<br>
strSQL = strSQL & &quot;[QueryFieldA]=[FormField1] and &quot;<br>
End if (etc.)<br>
<br>
You can test for the last &quot;and&quot; and remove if needed *before* your final parm with<br>
If Left(strSQL, Len(strSQL)-4) = &quot;and &quot; then<br>
strCriteria = Left(strSQL, Len(strSQL)-4)<br>

 

[elizabeth]

Should've looked before I leapt here! Gave you the wrong example. That was for creating a report, you're trying to put data into the form. Probably a better approach is to build the underlying query (strSQL) with your if statements, then refresh the form. I'm out of my comfort zone now, but the following may be of some help... <br>
<br>
strSQL = &quot;SELECT (blah blah blah...) WHERE &quot;<br>
If Not IsNull([FormFieldField1]) Then<br>
strSQL = strSQL & &quot;[QueryFieldA]=[FormField1] and &quot;<br>
End if (etc.)<br>
<br>
You can test for the last &quot;and&quot; and remove if needed *before* your final parm with<br>
If Left(strSQL, Len(strSQL)-4) = &quot;and &quot; then<br>
strCriteria = Left(strSQL, Len(strSQL)-4)<br>

 

[dreew]

This has been a problem I've wound up duct taping a number of queries together to solve...but I think I found an easier way. The Nz() function has a description of &quot;Nz(variant[, valueifnull])&quot;, so is it possible a wildcard could be inserted as the value if null in the query?

 

[EuanPirie]

I found a solution which works well. I have a form which conatins no data, but has the combo boxes in it. There is a button for generating the report. When this is clicked, it runs a macro which calls the report. The report in turn opens, using a query which gets it's input data from the form. It is a little backward, but works very well. The query has criteria set up, as follows: forms![query_form]![userid] Or forms![query_form]![userid] is null<br>
<br>
Access changes this into a different format when you save and exit, then reopen. It looks messy when you go in, but sit down and look at it it makes sense. Just.<br>
<br>
It may not be the fastest way to run, or for that matter the easiest, but it does all it need to do. I actually found this in the MS KB, If your interested the URL is support.microsoft.com/support/kb/articles/Q95/9/31.asp. It also has some neat example of queries that can be used to generate certain outputs. Very helpful for a microsoft help file!<br>
<br>
Thank you all so much for your help, could not have got there without you.

 

[rochelle]

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.

 

[dreew]

Thanx Rochelle, I tried that criteria trick, and now can seriously cut down the number of queries in my application. <br>