Mutiple Criteria Reports

[Tammy]

I have posted this in the VB section as well because I am not exactly sure where it belongs, but here it goes....

I need to create a report that is based on multiple criteria that the user selects from a series of combo boxes on one form. For Example for a particular Product Report the user may select that he/she wants to see the Product Report for all Products which have a color "Black" , grade "B" and type "New"... how do I generate this report? As usual any help that someone can provide to help save my sanity would be greatly appreciated!! Thanks.

 

[AK]

Base your report on a query that uses parameters for each of the fields you need. When the report is opened, it will prompt the user and pull up the records based upon his or her input.

 

[Dfhaj]

Use a parameter query to solve this problem. I got it working this week myself.
(1) Create list boxes or text boxes on a form for data entry.
(2) Write an SQL query to search your tables with the form object names embedded in the condition parts of the query. See some sample code as follows:
SELECT P.proj_no, P.proj_name, P.proj_city, P.proj_state
FROM Project AS P, Client AS C, Personnel AS Per
WHERE P.proj_desc Like "*"& [Forms]![SmenuP]![List1] & "*"
AND P.proj_desc Like "*"&[Forms]![SmenuP]![List4] & "*"
AND P.proj_desc Like "*"& [Forms]![SmenuP]![text25] & "*"
AND P.proj_desc Like "*"& [Forms]![SmenuP]![text19] & "*"
AND P.proj_client_id = C.client_id
AND P.proj_pers_id_mgr = Per.pers_id
ORDER BY P.proj_no;

Also notice that the object properties are cocantenated on both sides with wildcard characters "*", allowing the query to search phrases for the text string selected by the user.
(3) Create a report based on your parameter query.
(4) Add a command button on your form to run to open the report (which will also trigger your query). Two key lines of VB code assocated with the command button (on click)should be like the following :
DoCmd.OpenReport "Report Name", acViewPreview
DoCmd.RunCommand acCmdZoom75

This code will open the report, run the query, and display the report on the screen for viewing sized at 75%. After, viewing you can click the print button on the menu bar to print.