I have a form with 3 combo controls to select the supplier group, year and
month. The supplier group is an ID in a supplier table which contains
multiple suppliers. I am trying to generate a Supplier Report based on each
supplier within the supplier group, year and month selected. This works fine
if I select an individual month in the month combo box list. The problem is I
have a "AllMonths" entry in the Month combo list which I would like to
include all month rows for the specific supplier and year in the report. I
have tried to set the MyMonth field to a Null and have tried to set the
MonthNo field to "Between 2 And 12". Neither of which worked. Following is a
couple of SELECT statements I have tried in the Supplier Report. hanks ahead
of time on any help and direction you can provide. 1) SELECT * FROM
qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(IIf(Forms!frmSupplierReportCardEmailForm!cboMonth ="AllMonths",(MonthNo
Between 2 And 11),(MyMonth=Forms!frmSupplierReportCardEmailForm! cboMonth)))
And (SupplierNo=GetSupplierID());
2) SELECT * FROM qryPurchasingTable WHERE
IIf((Forms!frmSupplierReportCardEmailForm!cboMonth ="AllMonths"),(((MyYear=Forms!frmSupplierReportCar dEmailForm!cboYear)
Or (Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(SupplierNo=GetSupplierID())),((MyYear=Forms!frmSu pplierReportCardEmailForm!cboYear)
Or (Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((MyMonth=Forms!frmSupplierReportCardEmailForm!cbo Month) Or
(Forms!frmSupplierReportCardEmailForm!cboMonth Is Null)) And
(SupplierNo=GetSupplierID()));
Note the following SELECT statement works for generating a Supplier Report
for a different form -- SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((MyMonth=Forms!frmSupplierReportCardEmailForm!cbo Month) Or
(Forms!frmSupplierReportCardEmailForm!cboMonth Is Null)) And
(SupplierNo=GetSupplierID());
--
frank-a