Filter Report by Form 1

[cydud3]

I know there's a lot of stuff around about this topic but I haven't found exactly what I needed. Here's the situation: I have a report which query source has the criteria [Forms]![frmTest]![EmployeeId]. In the form frmTest, I have a combo box which displays the EmployeeId (bound column) and the name. A button calls up the report which successfully generates the report containing only the specific employee. However, I also want to be able to have the option All where the report generates for all employees. For now, I've been going around this by having two reports....one with criteria and one without. This obviously makes it harder to do updates.

I also want to add not just EmployeeId to the list of variables but with the way I'm doing them right now, if you leave one combo box blank the report will probably not run because part of the WHERE clause will be blank.

Guys, any idea how to fix this? I'm not really big on VB but I can definitely follow instructions. Basically, I want to be able to use just one report for specific and "all" reports and be able to add more than one combo box for the form to get the variables.

 

[dRahme]

Hi, an easy way to accomplish this is to use a Union Query in your combo Box. Something like:

SELECT tblEmployees.EmpId, tblEmployees.Ln, tblEmployees.Fn
FROM tblEmployees
union
select "(ID)", "(All)","(Employees)" from tblemployees
order by tblemployees.empid

Add the following to your empID Criteria in the report:

Like "*" & IIf([forms]![frmEmployees]![cboEmpID]="(ID)","",[forms]![frmEmployees].[cboEmpID]) & "*"

which translates into if the individual selects (ID), all employees will print.

 

[cydud3]

Thanks for your suggestion. I will try it out and see if I can get it to work. I don't really understand the syntax of the union query but I hope it'll work.

 

[dRahme]

The first two lines are from the original query and they come from the employees table.

Next you Union another statement to the original query, giving you another select option, in this case an option for all employees.

The union statement has to contain the same number of fields as the original.

if you wrote

union
select "(Cats)", "(Dogs)","(Other)" from tblemployees

then you would see Cats, Dogs and Other as a select option in your combo box.

 

[cydud3]

Thanks for the explanation. You're explanation made a lot of sense when I actually tried setting it up. And by the way, it worked perfectly . Now, I'm assuming that this will work with queries with multiple criterias. That's the next thing I'm going to try.

 

[dRahme]

Yes, it will work with multiple criteria. Glad you got it working and thanks for the star.

 

[cydud3]

I ran into a bit of a problem with the multiple criteria. I hope somebody can help me with this. Basically, I now have a form that asks for 2 variables

Employee

OR

Category

Using the technique, above, I can use (All) fine. However, what I really need is (None). My combo box now has the following as source:

SELECT [Employee].Code, [Employee].Name FROM [Employees] UNION select "(CodeN)", "(None)" from [Employees] UNION select "(CodeA)", "(All)" from [Employees]
ORDER BY [Employees].Name;

My question is, how do you structure the criteria so it distinguishes between None and All since the operator is OR. As an example: IF Employee is set to some EmployeeID and Category is set to None, report should only print for that specific employee. If Employee is set to None and Category to some specific CategoryID, report should print for all employees within that category. I hope I'm making sense

 

[dRahme]

Hi, sorry. Didn't get a chance to test this - have a plane to catch but this should be close. Expand the criteria to include the 'Or'

SELECT Employees.Name, Employees.Code
FROM Employees
WHERE (((Employees.Name) Like "*" & IIf([forms]![frmEmployees]![cboEmpID]="(CodeA)","",[forms]![frmEmployees].[cboEmpID]) & "*" Or (Employees.Name)=IIf([forms]![frmEmployees]![cboEmpID]="(CodeN)","",[forms]![frmEmployees].[cboEmpID]) & ""));

Hope that helps. Happy holidays at least.

dRahme

 

[dRahme]

Whoops - got in too big a hurry. The criteria field in the query:

Like "*" & IIf([forms]![frmEmployees]![cboEmpID]="(CodeA)","",[forms]![frmEmployees].[cboEmpID]) & "*" Or IIf([forms]![frmEmployees]![cboEmpID]="(CodeN)","",[forms]![frmEmployees].[cboEmpID]) &

 

[cydud3]

Thanks so much. It works

 

[dRahme]

Good deal. One thing I would do if you haven't already is build a little library for saving useful tidbits of sql strings and other code for future use. Took me a while to figure out that my memory wasn't as good as I thought it was.