Reports filtered from a form

[André Brasil]

Hi there,

I´m using Access 2007 and I´ve created a database, modified from an office
template (Faculty List), where I manage a list of doctors and dentists. The
idea is to be able to insert the data, with specialties, location, insurance
info, etc. Then, I want to be able to generate reports from the filters
applied on the main form, in datasheet style. The point is: nothing I try
works.

I´ve tried inputing on the macro that opens the reports (Where condition),
something in the structure:

![Field]=[Forms]![Form]![Field], but it
works only partially, showing only one of the filters applied. For example,
if I filter by specialty, listing psycologists and neurologists on my form,
the report shows only neurologists. More than that, if I don´t filter at all,
it shows only the first specialty on the list.

Can someone help me to transfer the filters applied on the form to my reports?

Thanks.

 

[Allen Browne]

Andre, download this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

It demonstrates how to build a filter string in code, from the boxes where
the user entered something.

The example shows the results in a form, but the code is identical to build
the WhereCondition for OpenReport.

 

[André Brasil]

Thank you for your help, and I think this is the way to go: building the
WHERE string dinamically, but the example I´ve downloaded has a problem for
me.

The fact is that I don´t have any problems filtering by different fields,
the trouble is to filter by more than one value in the same field. The
example would be to filter all the Doctors living in NY, LA and Atlanta. Even
though I could get inspired by the code of the example, I´m a newbie in
programming, and I have no idea where to start.

Thanks.

 

[Allen Browne]

An unbound multi-select list box will let the user select multiple values to
use in one field. You then loop through the ItemsSelected, and use an IN
operator in the WHERE clause, e.g.:
WHERE SomeField IN (1,45, 98);

For an example see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

To combine that with other filters, just use AND between the phrases of the
WHERE clause.

 

[André Brasil]

Once again, I believe we´re on our way. I opened the Northwind database,
checked the description on your page, but there is still one little problem:
there´s no "product by category" query. The closest I could find was "product
sales by category", but I don´t think it is the same. Besides, there is no
table, form, query, report or whatever named "category".

I believe the problem is that I use Access 2007, and the latest Northwind
database. Is there any way around?

Thanks

 

[Allen Browne]

The Northwind 2007 is different.

This is the query from the old version (if it's any use, because the
structure may be different too):

SELECT Categories.CategoryName, Products.ProductName,
Products.QuantityPerUnit, Products.UnitsInStock,
Products.Discontinued, Categories.CategoryID
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)<>Yes))
ORDER BY Categories.CategoryName, Products.ProductName;