If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to select all months from a table to include in a report
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 |
Thread Tools | |
Display Modes | |
|
|