How to select all months from a table to include in a report

[FSHOTT]

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!cboYear) 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!frmSupplierReportCardEmailForm!cboYear)
Or (Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(SupplierNo=GetSupplierID())),((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear)
Or (Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth) 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!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth) Or
(Forms!frmSupplierReportCardEmailForm!cboMonth Is Null)) And
(SupplierNo=GetSupplierID());

 

[Wolfgang Kais]

Hello Frank.

[snip] 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 [snip]
1) SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(IIf(Forms!frmSupplierReportCardEmailForm!cboMonth="AllMonths",
(MonthNo Between 2 And 11),
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth)))
And (SupplierNo=GetSupplierID()); [snip]

SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((Forms!frmSupplierReportCardEmailForm!cboMonth="AllMonths") Or
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth)) And
(SupplierNo=GetSupplierID());

 

[FSHOTT]

Wolfgang Thank You for the response. Unfortunately it does not generate a
report for all the months of the specified year. The SELECT query has 0 rows
instead of all the month rows of data for the ID'd year.
--
frank-a

Hello Frank.

[snip] 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 [snip]
1) SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(IIf(Forms!frmSupplierReportCardEmailForm!cboMonth="AllMonths",
(MonthNo Between 2 And 11),
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth)))
And (SupplierNo=GetSupplierID()); [snip]

SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((Forms!frmSupplierReportCardEmailForm!cboMonth="AllMonths") Or
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth)) And
(SupplierNo=GetSupplierID());

--
Regards,
Wolfgang


.

 

[Wolfgang Kais]

Hello Frank.

[snip] 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 [snip]
1) SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(IIf(Forms!frmSupplierReportCardEmailForm!cboMonth="AllMonths",
(MonthNo Between 2 And 11),
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth)))
And (SupplierNo=GetSupplierID()); [snip]

SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((Forms!frmSupplierReportCardEmailForm!cboMonth="AllMonths") Or
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboMonth)) And
(SupplierNo=GetSupplierID());

Wolfgang Thank You for the response. Unfortunately it does not
generate a report for all the months of the specified year.
The SELECT query has 0 rows instead of all the month rows of data
for the ID'd year.

Now, I see that you seem have two month columns in your query:
MonthNo (seems to be the number of the month, an integer from 1 to 12)
and MyMonth, which has to be a value that can be compared to the
value that is selected in the ComboBox.
To find out, what values the query fields have and what the value
of the comboBox is, try this query first:

SELECT *, Forms!frmSupplierReportCardEmailForm!cboMonth
FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboYear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(SupplierNo=GetSupplierID());

Does the query return any records at all? If so, what are the values
of MyMonth, MonthNo and the last column? Try this with different
selections in the ComboBox, for example once with "January" and
then with "AllMonths". What does the last column show?