Report with Multiple Criteria

[stru]

Hi,

I have a report that is currently being generated based on 3 different selection criteria. I have created a form where the info is entered and passes the criteria along to the report. It works fine.

I would like to add an additional 2 fields for criteria. The problem I am having is the code I created will be too long with 5 criteria. Here is the code with the 3 criteria that currently works

SELECT tblArchive.Date1, tblArchive.DMM, tblArchive.Department, tblArchive.PDNo, tblArchive.SKU, tblArchive.ItemDescription, tblArchive.FirstCost, tblArchive.SuggestedRetail, tblArchive.Product, tblArchive.IMU, tblArchive.ELC, tblArchive.LandingFactor, tblArchive.LandingCosts, ([tblArchive].[Date1]) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED] AS Expr1 FROM tblArchive WHERE (((tblArchive.Date1) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED]) AND ((tblArchive.DMM)=[Forms]![frmDates]![cboDMM]) AND ((tblArchive.Department)=[Forms]![frmDates]![cboDpt])) OR (((tblArchive.Date1) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED]) AND ((tblArchive.DMM)=[Forms]![frmDates]![cboDMM]) AND (([Forms]![frmDates]![cboDpt]) Is Null)) OR (((tblArchive.Date1) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED]) AND ((tblArchive.Department)=[Forms]![frmDates]![cboDpt]) AND (([Forms]![frmDates]![cboDMM]) Is Null)) OR (((tblArchive.DMM)=[Forms]![frmDates]![cboDMM]) AND ((tblArchive.Department)=[Forms]![frmDates]![cboDpt]) AND ((([tblArchive].[Date1]) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED]) Is Null) AND (([Forms]![frmDates]![cboDpt]) Is Null)) OR (((tblArchive.DMM)=[Forms]![frmDates]![cboDMM]) AND ((([tblArchive].[Date1]) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED]) Is Null)) OR (((tblArchive.Department)=[Forms]![frmDates]![cboDpt]) AND ((([tblArchive].[Date1]) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED]) Is Null) AND (([Forms]![frmDates]![cboDMM]) Is Null)) OR (((tblArchive.Date1) Between [Forms]![frmDates]![txtSD] And [Forms]![frmDates]![txtED]) AND (([Forms]![frmDates]![cboDpt]) Is Null) AND (([Forms]![frmDates]![cboDMM]) Is Null));

I realize that this is inefficient, but I just tried to do what works. I need some code that will allow the report to be based on the 5 criteria and not just the 3. Does anyone have a suggestion as to how I should/could go about this? Thanks.

 

[Remou]

This may help:
Build Report Criteria via a Form w/list box, text box, date range
faq181-5497