Requery parameter filter in a report?

[LHaun]

Hello,



I am fairly new to access and all my knowledge of access has been gathered from google and websites like this. I have created a report that shows the employees' id, first and last name, and shift. I have a button called "Filter by employee ID" on my report that, when clicked, opens a parameter dialog box and allows a user to enter the employeeid of choice. The records are then filtered to show only those that contain the employeeid entered into the parameter dialog box. I have another button that, when clicked, shows all records. But here's my problem: If I click the "Filter by employee id" button and enter my employee id of choice, then click show all records, and then click "filter by employee id", no parameter dialog box appears and it only toggles the filter for my last entry. Essentially, I want to be able to enter a new employee id every time I click the filter by employee id button rather than only toggle my last entry.

Thanks for the help,

- Logan

[ranman256]

Then the report query needs a parameter box.
In the criteria box under the ID field, put:
[Enter EmpID]

the report (query) will then ask.

[LHaun]

Ranman,

If I enter the parameter into the query, the problem I have is that my show all records macro won't be effective because my report's record source is the query which is now always filtered by the employeeid parameter. I want the report by default to show all the employees' records and then if you want to see or print only a certain employee's report, you filter it; and then if you want to view another employee's record or print it; you filter it again.

Hope that makes sense,

- Logan

[rpeare]

put a field in your query

Let's say your form is named 'frmSearch'
Let's say the field containing your search value is 'fldSearch'

In your query driving your report put in an additional field:


Show: iif(not isnull([forms]![frmSearch]![fldSearch]) and [forms]![frmSearch]![fldSearch] = [EmployeeID], 1, iif(isnull([forms]![frmSearch]![fldSearch]), 1, 0))

Then put a criteria of 1 in the newly created SHOW field.

Then if the search field is blank, everyone shows, if the search field is filled, the report will only show the relevant employee.

I think other folks use filter commands but I don't like doing that, I'd prefer to use criteria on queries.

[LHaun]

Gentlemen,

I appreciate all your help. I ended up creating with a simple solution:
On my report I added a button called "Filter". When you click the filter button a form pops up. On the pop up form there are two buttons which are "Filter by Shift" and "Filter by Employee". When you select either of the buttons, it closes the report and the form then opens the report with condition of [EmployeeID]=[EnterEmployeeID] or [Shift]=[EnterShift] based on which selection you made.

The downside to this is that the database has to reload the report every time you choose a filter. I don't think this will be a problem however, because the report will only display 32 records max when filtered.