A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 06:27 PM posted to microsoft.public.access
FSHOTT[_2_]
external usenet poster
 
Posts: 58
Default 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
  #2  
Old June 1st, 2010, 10:16 PM posted to microsoft.public.access
Wolfgang Kais[_4_]
external usenet poster
 
Posts: 18
Default How to select all months from a table to include in a report

Hello Frank.

"FSHOTT" wrote:
[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!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(IIf(Forms!frmSupplierReportCardEmailForm!cboMonth ="AllMonths",
(MonthNo Between 2 And 11),
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboM onth)))
And (SupplierNo=GetSupplierID()); [snip]


SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((Forms!frmSupplierReportCardEmailForm!cboMonth="A llMonths") Or
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboM onth)) And
(SupplierNo=GetSupplierID());

--
Regards,
Wolfgang


  #3  
Old June 2nd, 2010, 06:38 PM posted to microsoft.public.access
FSHOTT[_2_]
external usenet poster
 
Posts: 58
Default How to select all months from a table to include in a report

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


"Wolfgang Kais" wrote:

Hello Frank.

"FSHOTT" wrote:
[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!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(IIf(Forms!frmSupplierReportCardEmailForm!cboMonth ="AllMonths",
(MonthNo Between 2 And 11),
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboM onth)))
And (SupplierNo=GetSupplierID()); [snip]


SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((Forms!frmSupplierReportCardEmailForm!cboMonth="A llMonths") Or
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboM onth)) And
(SupplierNo=GetSupplierID());

--
Regards,
Wolfgang


.

  #4  
Old June 3rd, 2010, 08:05 AM posted to microsoft.public.access
Wolfgang Kais[_4_]
external usenet poster
 
Posts: 18
Default How to select all months from a table to include in a report

Hello Frank.

"FSHOTT" wrote:
[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!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
(IIf(Forms!frmSupplierReportCardEmailForm!cboMonth ="AllMonths",
(MonthNo Between 2 And 11),
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboM onth)))
And (SupplierNo=GetSupplierID()); [snip]


Wolfgang Kais wrote:
SELECT * FROM qryPurchasingTable WHERE
((MyYear=Forms!frmSupplierReportCardEmailForm!cboY ear) Or
(Forms!frmSupplierReportCardEmailForm!cboYear Is Null)) And
((Forms!frmSupplierReportCardEmailForm!cboMonth="A llMonths") Or
(MyMonth=Forms!frmSupplierReportCardEmailForm!cboM onth)) And
(SupplierNo=GetSupplierID());


"FSHOTT" wrote:
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!cboY ear) 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?

--
Regards,
Wolfgang


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.