Help with IIF expression

Hi,
I’m trying to get my column heading in my report to show the month from the
parameter input. I’m not sure how to write this expression with the
following expression contained in my query:

ODCPrevious: Sum(IIf([Mnth]=2,[ODC_Cost],0))

Many thanks,
Kay

Where does the parameter fit into the expression? Is Mnth the parameter for
which the user is prompted?

AccessKay wrote:
Hi,
I’m trying to get my column heading in my report to show the month from the
parameter input. I’m not sure how to write this expression with the
following expression contained in my query:

ODCPrevious: Sum(IIf([Mnth]=2,[ODC_Cost],0))

Many thanks,
Kay

--
Message posted via http://www.accessmonster.com

Hi Bruce,

I usually post from the Microsoft forum but it doesn't appear to be working.
I thought I'd check here and glad I did.

I hope I can explain this...I set up two queries initially, one for current
month and one for previous month and put a field in called Mnth:1 and Mnth:2
(assigning numbers 1 or 2). My expression below is for Mnth:2 or the Previous
Month. Then I did a Union query and merged the two. And then I did my final
query that shows the expression that I typed below. So, the parameter is
located in the query that I created first for previous month under MnthC
[TransDate]). But for some reason the MnthC: disappears when I reopen the
query. I hope you understand. TransDate is my full date for each
transaction.

Thanks,
Kay


BruceM wrote:
Where does the parameter fit into the expression? Is Mnth the parameter for
which the user is prompted?

Hi,
I’m trying to get my column heading in my report to show the month from the
[quoted text clipped - 5 lines]
Many thanks,
Kay

You seem to be taking the long way around, but I can't quite understand what
you are trying to do. The thing that has me especially puzzled is that you
have created queries for the previous month and the current month, but then
added another field that is a fixed value. Please post the SQL for the Mnth:
1 query. To do that, open the query. Click View SQL. Copy what you see
there, and post it here.

It seems you have Mnth: 1 and Mnth: 2 queries, and a union query to put them
together, but then there is a final query, I think.

When referring to queries in postings it would be best to give them names.
You mention "the query that I created first for previous month under MnthC",
but I can't tell if this is yet another query, or the Mnth: 1 query, or
something else; and I don't know what MnthC is.

AccessKay wrote:
Hi Bruce,

I usually post from the Microsoft forum but it doesn't appear to be working.
I thought I'd check here and glad I did.

I hope I can explain this...I set up two queries initially, one for current
month and one for previous month and put a field in called Mnth:1 and Mnth:2
(assigning numbers 1 or 2). My expression below is for Mnth:2 or the Previous
Month. Then I did a Union query and merged the two. And then I did my final
query that shows the expression that I typed below. So, the parameter is
located in the query that I created first for previous month under MnthC
[TransDate]). But for some reason the MnthC: disappears when I reopen the
query. I hope you understand. TransDate is my full date for each
transaction.

Thanks,
Kay

Where does the parameter fit into the expression? Is Mnth the parameter for
which the user is prompted?
[quoted text clipped - 4 lines]
Many thanks,
Kay

--
Message posted via http://www.accessmonster.com

Bruce,

This is confusing…I know. Sorry about that. I think it might be best to
give you the SQL from each query. Thanks for looking at this! If you happen
to know a better way to do this, then I'm all for it because I have to do the
same for year and quarter and then again by a different category.

Name: bqODCVar1

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth
FROM tblTrans_Mstr
WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year(
[TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter
Current QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1
HAVING (((tblTrans_Mstr.Group)="ODC"));

Name: bqODCVar2

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth
FROM tblTrans_Mstr
WHERE (((([TransDate])) Like [Enter Previous Month] & "*") AND ((Year(
[TransDate]))=[Enter Current or Previous Year]) AND ((Format([TransDate],"q"))
=[Enter Previous QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2
HAVING (((tblTrans_Mstr.Group)="ODC"));

Name: bqODCVar (this is my Union Query…as you will see)

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth
FROM tblTrans_Mstr
WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year(
[TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter
Current QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1
HAVING (((tblTrans_Mstr.Group)="ODC"));
UNION ALL SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category,
tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth
FROM tblTrans_Mstr
WHERE (((Month([TransDate])) Like [Enter Previous Month] & "*") AND ((Year(
[TransDate]))=[Enter Previous Year]) AND ((Format([TransDate],"q"))=[Enter
Previous QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2
HAVING (((tblTrans_Mstr.Group)="ODC"));

Name: qryODCVariance (my final query that I’m using for my report)

SELECT bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar.
PA_Descriptions, bqODCVar.Credit_GL_Acct, Sum(IIf([Mnth]=2,[ODC_Cost],0)) AS
ODCPrevious, Sum(IIf([Mnth]=1,[ODC_Cost],0)) AS ODCCurrent, [ODCCurrent]-
[ODCPrevious] AS ODCDelta
FROM bqODCVar
GROUP BY bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar.
PA_Descriptions, bqODCVar.Credit_GL_Acct
HAVING (((bqODCVar.Group)="ODC"));


BruceM wrote:
You seem to be taking the long way around, but I can't quite understand what
you are trying to do. The thing that has me especially puzzled is that you
have created queries for the previous month and the current month, but then
added another field that is a fixed value. Please post the SQL for the Mnth:
1 query. To do that, open the query. Click View SQL. Copy what you see
there, and post it here.

It seems you have Mnth: 1 and Mnth: 2 queries, and a union query to put them
together, but then there is a final query, I think.

When referring to queries in postings it would be best to give them names.
You mention "the query that I created first for previous month under MnthC",
but I can't tell if this is yet another query, or the Mnth: 1 query, or
something else; and I don't know what MnthC is.

Hi Bruce,

[quoted text clipped - 19 lines]
Many thanks,
Kay

It seems the first three queries are intended to retrieve records from this
month and last month. The way the parameter is set up the user could specify
any two months (one in each query), but since the prompt is to enter the
current month, year, and quarter (?), it seems you mean for the user to
select the months and years you have specified. If that is the case, there
is no need for the user to enter a parameter. You could replace the first
three queries with this:

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth
FROM tblTrans_Mstr
WHERE tblTrans_Mstr.[TransDate] = DateSerial(Year(Date()),Month(Date())-1,1)
AND tblTrans_Mstr.Group = "ODC"

I left out the grouping, but you could add it back if it is needed.

For the report you could group by month. Go to View Sorting and Grouping.
In Field/Expression enter on the top line:

Month(TransDate)

Sort Ascending

In the next line:

Format([TransDate],"mmmm")

Select Group Header and Group Footer at the bottom of the dialog box, and
close the dialog box.

In the Report, place in the group header a text box bound to TransDate. Set
its format to "mmmm".

In the group footer, place a combo box with its Control Source set to:

=Sum([TransDate])

This is one of several options you have. It can be refined or adjusted as
needed. In any case, the report can use the simple query I showed. If the
idea is to show a different range of months than this month and last month,
that can be done. Non-contiguous months are more difficult, but can be done
also.

Access has a lot of built-in capabilities that can be harnessed to make your
task simpler. Perhaps I have misread the situation, but I am quite certain
now that you were taking the long way around.

AccessKay wrote:
Bruce,

This is confusing…I know. Sorry about that. I think it might be best to
give you the SQL from each query. Thanks for looking at this! If you happen
to know a better way to do this, then I'm all for it because I have to do the
same for year and quarter and then again by a different category.

Name: bqODCVar1

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth
FROM tblTrans_Mstr
WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year(
[TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter
Current QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1
HAVING (((tblTrans_Mstr.Group)="ODC"));

Name: bqODCVar2

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth
FROM tblTrans_Mstr
WHERE (((([TransDate])) Like [Enter Previous Month] & "*") AND ((Year(
[TransDate]))=[Enter Current or Previous Year]) AND ((Format([TransDate],"q"))
=[Enter Previous QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2
HAVING (((tblTrans_Mstr.Group)="ODC"));

Name: bqODCVar (this is my Union Query…as you will see)

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth
FROM tblTrans_Mstr
WHERE (((([TransDate])) Like [Enter Current Month] & "*") AND ((Year(
[TransDate]))=[Enter Current Year]) AND ((Format([TransDate],"q"))=[Enter
Current QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1
HAVING (((tblTrans_Mstr.Group)="ODC"));
UNION ALL SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category,
tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2 AS Mnth
FROM tblTrans_Mstr
WHERE (((Month([TransDate])) Like [Enter Previous Month] & "*") AND ((Year(
[TransDate]))=[Enter Previous Year]) AND ((Format([TransDate],"q"))=[Enter
Previous QTR]))
GROUP BY tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 2
HAVING (((tblTrans_Mstr.Group)="ODC"));

Name: qryODCVariance (my final query that I’m using for my report)

SELECT bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar.
PA_Descriptions, bqODCVar.Credit_GL_Acct, Sum(IIf([Mnth]=2,[ODC_Cost],0)) AS
ODCPrevious, Sum(IIf([Mnth]=1,[ODC_Cost],0)) AS ODCCurrent, [ODCCurrent]-
[ODCPrevious] AS ODCDelta
FROM bqODCVar
GROUP BY bqODCVar.Category, bqODCVar.Group, bqODCVar.Product, bqODCVar.
PA_Descriptions, bqODCVar.Credit_GL_Acct
HAVING (((bqODCVar.Group)="ODC"));

You seem to be taking the long way around, but I can't quite understand what
you are trying to do. The thing that has me especially puzzled is that you
[quoted text clipped - 16 lines]
Many thanks,
Kay

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

Bruce,

I got stuck on the part where you said “On the next line� where I should
enter Format([TransDate],"mmmm"). I don’t think I have a dialog box. I’m
using 2007 so all that sorting and grouping stuff is at the bottom.

I put the rest in though. I never knew you could put a combo box in a report!
I’m not yet sure of its purpose because it’s a number I don’t recognize (not
the sum of ODC_Cost). And that’s neat how the quarter shows up.

But this isn’t what I wanted to do in this case though I hope to use this for
my current month reports.
I was needing to build a variance report where the user can pick any two
months and the report will show those months along with the variance. I have
to also do this for Current Month in Current Year compared with the same
Previous Year’s Month. And then the current Qtr vs the previous year’s Qtr.
That’s how I came up with my complicated assortment of queries

I don’t want to take up too much of your time. So if it’s too long of a
process to make suggestions about how I can get what I just mentioned, then I
just need to know how in my report, I can get the Headings for the ODC Cost
to show the months chosen from the parameter. I wish I knew how to do this
better. I’ve read all kinds of stuff but can’t get anything specific to what
I’m trying to do.

I really do appreciate the help!


BruceM wrote:
It seems the first three queries are intended to retrieve records from this
month and last month. The way the parameter is set up the user could specify
any two months (one in each query), but since the prompt is to enter the
current month, year, and quarter (?), it seems you mean for the user to
select the months and years you have specified. If that is the case, there
is no need for the user to enter a parameter. You could replace the first
three queries with this:

SELECT tblTrans_Mstr.TransDate, tblTrans_Mstr.Category, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.
Credit_GL_Acct, tblTrans_Mstr.ODC_Cost, 1 AS Mnth
FROM tblTrans_Mstr
WHERE tblTrans_Mstr.[TransDate] = DateSerial(Year(Date()),Month(Date())-1,1)
AND tblTrans_Mstr.Group = "ODC"

I left out the grouping, but you could add it back if it is needed.

For the report you could group by month. Go to View Sorting and Grouping.
In Field/Expression enter on the top line:

Month(TransDate)

Sort Ascending

In the next line:

Format([TransDate],"mmmm")

Select Group Header and Group Footer at the bottom of the dialog box, and
close the dialog box.

In the Report, place in the group header a text box bound to TransDate. Set
its format to "mmmm".

In the group footer, place a combo box with its Control Source set to:

=Sum([TransDate])

This is one of several options you have. It can be refined or adjusted as
needed. In any case, the report can use the simple query I showed. If the
idea is to show a different range of months than this month and last month,
that can be done. Non-contiguous months are more difficult, but can be done
also.

Access has a lot of built-in capabilities that can be harnessed to make your
task simpler. Perhaps I have misread the situation, but I am quite certain
now that you were taking the long way around.

Bruce,

[quoted text clipped - 72 lines]
Many thanks,
Kay

I don't have Access 2007 here. I'll try to take a look tonight to see how
this works in Access 2007. Maybe somebody else will jump in.

AccessKay wrote:
Bruce,

I got stuck on the part where you said “On the next line� where I should
enter Format([TransDate],"mmmm"). I don’t think I have a dialog box. I’m
using 2007 so all that sorting and grouping stuff is at the bottom.

I put the rest in though. I never knew you could put a combo box in a report!
I’m not yet sure of its purpose because it’s a number I don’t recognize (not
the sum of ODC_Cost). And that’s neat how the quarter shows up.

But this isn’t what I wanted to do in this case though I hope to use this for
my current month reports.
I was needing to build a variance report where the user can pick any two
months and the report will show those months along with the variance. I have
to also do this for Current Month in Current Year compared with the same
Previous Year’s Month. And then the current Qtr vs the previous year’s Qtr.
That’s how I came up with my complicated assortment of queries

I don’t want to take up too much of your time. So if it’s too long of a
process to make suggestions about how I can get what I just mentioned, then I
just need to know how in my report, I can get the Headings for the ODC Cost
to show the months chosen from the parameter. I wish I knew how to do this
better. I’ve read all kinds of stuff but can’t get anything specific to what
I’m trying to do.

I really do appreciate the help!

It seems the first three queries are intended to retrieve records from this
month and last month. The way the parameter is set up the user could specify
[quoted text clipped - 49 lines]
Many thanks,
Kay

--
Message posted via http://www.accessmonster.com

I made a mistake. I should have said text box, not combo box. There is not
much point to a combo box on a report.

I checked Access 2007 at home, but by mistake I checked the Index dialog
rather than Sorting and Grouping. However, I found this in a posting:
"From the report design, click on the Design tab in the ribbon and then
the 'Group and Sort' icon.
You will see the sorting and grouping levels at the bottom of the screen
-- you can define them by clicking on the 'Add a Group' or 'Add a Sort'
button"

A direct way to get non-contiguous months is to have something like this as
the criteria for the date field:

(Between [Month 1 start] And [Month 1 end]) Or (Between [Month 2 start] And
[Month 2 end])

It would be necessary to use the complete date.

Better would be to make an unbound form frmParam, on which you would have 2
unbound combo boxes for month and another 2 for year. Make a two-field Month
table like this:

MoNum MoName
1 January
2 February
etc.

Create a query based on the table. Sort ascending on MoNum. Use the query
as the Row Source for the month combo boxes. Set the Bound Column to 1, the
Column Count to 2, and the Column Widths to something like 0";1".

For the Year combo boxes, make a query something like:

SELECT DISTINCT Year([TransDate])
AS TransYear
FROM TableName
ORDER BY Year([TransDate])

Use the query as the Row Source for the year combo boxes.

The month combo boxes are cboMo1, cboMo2. The year combo boxes are cboY1 and
cboY2. Call them what you like, but these are the names I will use.

In the report's Open event:

DoCmd OpenForm "frmParam",WindowMode:=acDialog

Have the user select the first and second month and year. Place a command
button on frmParam with the Click event:
Me.Form.Visible = False

In the query, the criteria for TransDate:

Between (DateSerial(Forms!frmParam!cboY1,Forms!frmParam!cb oMo1,1) And
DateSerial(Forms!frmParam!cboY1,Forms!frmParam1!cb oMo1 + 1,0)) Or Between
(DateSerial(Forms!frmParam!cboY2,Forms!frmParam1!c boMo2,1) And DateSerial
(Forms!frmParam!cboY2,Forms!frmParam!cboMo2 + 1,0))

See Help for information about DateSerial.

In the report's Close event:

DoCmd.Close acForm,"frmParam"

There are other ways you could do this. It could be that some of them
improve on what I have suggested. Also, this could be refined so that, for
instance, there are default month and years in the combo boxes on frmParam.

For comparing quarters you could use DatePart. See Help for more information.
I won't add anything more to this posting, as there is probably enough now to
keep you busy for a little while.

AccessKay wrote:
Bruce,

I got stuck on the part where you said “On the next line� where I should
enter Format([TransDate],"mmmm"). I don’t think I have a dialog box. I’m
using 2007 so all that sorting and grouping stuff is at the bottom.

I put the rest in though. I never knew you could put a combo box in a report!
I’m not yet sure of its purpose because it’s a number I don’t recognize (not
the sum of ODC_Cost). And that’s neat how the quarter shows up.

But this isn’t what I wanted to do in this case though I hope to use this for
my current month reports.
I was needing to build a variance report where the user can pick any two
months and the report will show those months along with the variance. I have
to also do this for Current Month in Current Year compared with the same
Previous Year’s Month. And then the current Qtr vs the previous year’s Qtr.
That’s how I came up with my complicated assortment of queries

I don’t want to take up too much of your time. So if it’s too long of a
process to make suggestions about how I can get what I just mentioned, then I
just need to know how in my report, I can get the Headings for the ODC Cost
to show the months chosen from the parameter. I wish I knew how to do this
better. I’ve read all kinds of stuff but can’t get anything specific to what
I’m trying to do.

I really do appreciate the help!

It seems the first three queries are intended to retrieve records from this
month and last month. The way the parameter is set up the user could specify
[quoted text clipped - 49 lines]
Many thanks,
Kay

--
Message posted via http://www.accessmonster.com

Thank you so much Bruce. Before I saw your post, I put a new thread out
there to find a way to build a parameter form to avoid inserting paramenters
into the query. I'm going to take a look at what you sent in detail and let
you know how it goes.

This is a big help to me and I really appreciate it!


BruceM wrote:
I made a mistake. I should have said text box, not combo box. There is not
much point to a combo box on a report.

I checked Access 2007 at home, but by mistake I checked the Index dialog
rather than Sorting and Grouping. However, I found this in a posting:
"From the report design, click on the Design tab in the ribbon and then
the 'Group and Sort' icon.
You will see the sorting and grouping levels at the bottom of the screen
-- you can define them by clicking on the 'Add a Group' or 'Add a Sort'
button"

A direct way to get non-contiguous months is to have something like this as
the criteria for the date field:

(Between [Month 1 start] And [Month 1 end]) Or (Between [Month 2 start] And
[Month 2 end])

It would be necessary to use the complete date.

Better would be to make an unbound form frmParam, on which you would have 2
unbound combo boxes for month and another 2 for year. Make a two-field Month
table like this:

MoNum MoName
1 January
2 February
etc.

Create a query based on the table. Sort ascending on MoNum. Use the query
as the Row Source for the month combo boxes. Set the Bound Column to 1, the
Column Count to 2, and the Column Widths to something like 0";1".

For the Year combo boxes, make a query something like:

SELECT DISTINCT Year([TransDate])
AS TransYear
FROM TableName
ORDER BY Year([TransDate])

Use the query as the Row Source for the year combo boxes.

The month combo boxes are cboMo1, cboMo2. The year combo boxes are cboY1 and
cboY2. Call them what you like, but these are the names I will use.

In the report's Open event:

DoCmd OpenForm "frmParam",WindowMode:=acDialog

Have the user select the first and second month and year. Place a command
button on frmParam with the Click event:
Me.Form.Visible = False

In the query, the criteria for TransDate:

Between (DateSerial(Forms!frmParam!cboY1,Forms!frmParam!cb oMo1,1) And
DateSerial(Forms!frmParam!cboY1,Forms!frmParam1!c boMo1 + 1,0)) Or Between
(DateSerial(Forms!frmParam!cboY2,Forms!frmParam1! cboMo2,1) And DateSerial
(Forms!frmParam!cboY2,Forms!frmParam!cboMo2 + 1,0))

See Help for information about DateSerial.

In the report's Close event:

DoCmd.Close acForm,"frmParam"

There are other ways you could do this. It could be that some of them
improve on what I have suggested. Also, this could be refined so that, for
instance, there are default month and years in the combo boxes on frmParam.

For comparing quarters you could use DatePart. See Help for more information.
I won't add anything more to this posting, as there is probably enough now to
keep you busy for a little while.

Bruce,

[quoted text clipped - 28 lines]
Many thanks,
Kay

--
Message posted via http://www.accessmonster.com