Dialog box as source for a Query Filter

[hermhart]

I want to use a dialog box (form) to enter a value which will be used as criteria in a query. I could put an expression into the criteria cell of the field to filter such as:
forms!dialog!manager
So then I can select for which manager I want to filter the query records. This basically works OK. My question is, How can I include the wild card in this, thus allowing me to opt to not have a filter (that is, to print all the records)? I tried putting an IIf() function in the criteria cell, something like:
IIf(forms!dialog!manager Like "*","",forms!dialog!manager)
This doesn't work. I tried some other variations on this, but without success. Any suggestions?

[LICKY]

If you are using a QBE query then you can put the following in the selection criteria

Like '*' & [Enter the manager's name] & '*'

Thus when the query is run an input box will pop asking for a manager's name.

[hermhart]

I think that would work if I didn't need to use a dialog box. But I want to keep my dialog box so I can just select a manager. That way the user doesn't have any spelling issues, etc.

[LICKY]

For the type of enquiries that you want I tend to use a combo box as per the following example

[Forms]![frm_Switchboard]![cboReport_TeamNumber]

It works just as well using a text box, but you would need to use the wild card format

Like '*' &[Forms]![frm_Switchboard]![cboReport_TeamNumber] &'*'

The combo box in this example is an unbound combo box

[hermhart]

Stunning. That works like a charm. Much obliged.

[hermhart]

OK, yes that works. I could make it serve my purpose if I put an empty record in the combo box of the dialog box. Then when I select that empty record, in effect the filter goes off and all records are displayed. I'd like to go one step further and, say, have a record in the combo box with content "All". (That would be more userfriendly and it would also allow me to let the field in the combo box's source table be a primary key, which is what I'd prefer.) Then I might put the following into the criteria cell:
IIf([forms]![frmDialog]![cboManager]="All",Like'*',([forms]![frmDialog]![cboManager]). This works if I select a real manager from the list. But selecting "All" does NOT give me a successful wildcard. That middle TRUE portion seems to be the key. Is the format wrong? I tried "", "*", Like '*', Like "*", Is Not Null, or just nothing at all between the commas.
No go. The fact that a real manager works OK tells me that in general I'm OK to use the IIf() function in this way.
If there's something basically wrong with this I can resort to your solution above.

[hermhart]

I think I have a solution. I create a calculated field in the query and call it Exp1:. (Let Table1 be the table on which my query is based.) Then I use my IIf() expression to define that field, thus: Exp1: IIf(forms!frmdialog!cboManager="All",Table1!Manage r Like "*",Table1!Manager Like forms!frmdialog!cboManager). Exp1 will now evaluate as True or False for each record. If forms!frmdialog!cboManager="All" then exp1 evaluates to true for all records. If forms!frmdialog!cboManager="Bob", for example, then for those records having Bob in the Table1!Manager field, exp1 evaluates to True. Else exp1 evaluates to False. I admit, I do not understand fully why this works. I was surprised to see exp1 returning Yes/No values rather than the Manager values. But the bottom line is...IT WORKS.

[JaneAFan]

I'm trying to do the same thing as hermhart, but I'm afraid my SQL isn't very good and the solution that you seem to have found isn't working really for me. Let me give my concrete example.

I have a form that works as a filter for a report - an unbound combo box that gets its values from a table. The old SQL for the query looked like this:

SELECT tblTeamMembers.MemberName, tblTeamMembers.Rate1, tblTeamMembers.Rate2, tblTeamMembers.Rate3, tblProjects.ProjectCode, tblProjects.Project, tblProjects.StartDate, tblProjects.EndDate, tblProjects.Status, tblDetail.Hours, tblTimeCards.RealRate, tblTimeCards.PayPeriod, tblDetail.Expenses
FROM (tblTeamMembers INNER JOIN tblTimeCards ON tblTeamMembers.[Argent Team MembersID] = tblTimeCards.EmployeeID) INNER JOIN (tblProjects INNER JOIN tblDetail ON tblProjects.ProjectCode = tblDetail.ProjectCode) ON tblTimeCards.TCRptID = tblDetail.TCRptID
WHERE (((tblProjects.ProjectCode)=[Forms]![frmProjectParameters]![ProjectParameter]))
GROUP BY tblTeamMembers.MemberName, tblTeamMembers.Rate1, tblTeamMembers.Rate2, tblTeamMembers.Rate3, tblProjects.ProjectCode, tblProjects.Project, tblProjects.StartDate, tblProjects.EndDate, tblProjects.Status, tblDetail.Hours, tblTimeCards.RealRate, tblTimeCards.PayPeriod, tblDetail.Expenses;

and it works just fine, the form lists all the projects and I can choose 1 to limit my report. However, I too would like to be able to have the user, instead of choosing one of the projects from the dynamically-generated list, enter All and essentially remove the filter, and create the report using all records. I tried using hermhart's solution and it changed the SQL to this:

SELECT tblTeamMembers.MemberName, tblTeamMembers.Rate1, tblTeamMembers.Rate2, tblTeamMembers.Rate3, tblProjects.ProjectCode, tblProjects.Project, tblProjects.StartDate, tblProjects.EndDate, tblProjects.Status, tblDetail.Hours, tblTimeCards.RealRate, tblTimeCards.PayPeriod, tblDetail.Expenses, [Expr1] AS Expr1
FROM (tblTeamMembers INNER JOIN tblTimeCards ON tblTeamMembers.[Argent Team MembersID] = tblTimeCards.EmployeeID) INNER JOIN (tblProjects INNER JOIN tblDetail ON tblProjects.ProjectCode = tblDetail.ProjectCode) ON tblTimeCards.TCRptID = tblDetail.TCRptID
WHERE (((tblProjects.ProjectCode)=[Forms]![frmProjectParameters]![ProjectParameter]))
GROUP BY tblTeamMembers.MemberName, tblTeamMembers.Rate1, tblTeamMembers.Rate2, tblTeamMembers.Rate3, tblProjects.ProjectCode, tblProjects.Project, tblProjects.StartDate, tblProjects.EndDate, tblProjects.Status, tblDetail.Hours, tblTimeCards.RealRate, tblTimeCards.PayPeriod, tblDetail.Expenses
HAVING ((([Expr1])=IIf([Forms]![frmProjectParameters]![ProjectParameter]="All",[tblProjects]![ProjectCode] Like "*",[tblProjects]![ProjectCode] Like [Forms]![frmProjectParameters]![ProjectParameter])));

but when I try to look at this query in datasheet view, it says I have a circular reference and when I look in form view, the combo box is no longer populated.

Can anyone tell me where I went wrong?

[hermhart]

Several suggestions.
1. I have "All" as one of my records in the Combo Box's table, so if I want ALL, I pick ALL instead of one of the filter choices. I wasn't sure if you also were doing that or not.
2. Expr1 needs to have "Yes" as its criteria. The end of my SQL looks like this:
...WHERE (((IIf([forms]![frmMgr4SProjectsEng]![ManagerName]="All",[qrySprojects]![ManagerName] Like "*",[qrySprojects]![ManagerName] Like [forms]![frmMgr4SProjectsEng]![ManagerName]))=Yes));
3. But maybe more to the point, you show the IIf() function as a part of the combo box SQL. That's not correct. Leave your Combo Box as is. In my case I have two separate tables. One table of Managers which is my pick box that is used in the unbound Combo Box. The other table is my Projects each of which has a manager responsible for that project. I have a query that is based on the Projects table which feeds the report. In this Projects query is where I put Expr1 with its IIf() function and the Yes criteria. Can you apply that to your case? Does that make sense?

[JaneAFan]

Actually, I figured out a way that kind of combines both of your solutions, and is much easier and simpler than I was making it out to be. Where the old SQL query that my report uses was:

WHERE tblProjects.ProjectCode=[Forms]![frmProjectParameters]![ProjectParameter]

the new line in the query is:

WHERE (((tblProjects.ProjectCode)=[Forms]![frmProjectParameters]![ProjectParameter])) OR ((([Forms]![frmProjectParameters]![ProjectParameter])="All"))

and in the form itself, the default value for the combo box is All.

What was confusing me before is that I was trying to make changes in the combo box at first, instead of in the query. But this works!

Thanks for your help.

[hermhart]

Great So you're doing it without an IIf() function. Maybe I could simplify mine using that idea.