Help with IIF expression

[AccessKay]

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

 

[BruceM via AccessMonster.com]

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
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

 

[AccessKay]

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?

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

 

[BruceM via AccessMonster.com]

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,

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]

 

[AccessKay]

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
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]

 

[BruceM via AccessMonster.com]

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,

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]

 

[AccessKay]

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
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]

 

[BruceM via AccessMonster.com]

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.

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]

 

[BruceM via 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!cboMo1,1) And
DateSerial(Forms!frmParam!cboY1,Forms!frmParam1!cboMo1 + 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,

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]

 

[AccessKay via 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!

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!cboMo1,1) And
DateSerial(Forms!frmParam!cboY1,Forms!frmParam1!cboMo1 + 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]

 

[AccessKay via AccessMonster.com]

Hi Bruce,
My post seems to be buried but hopefully you’ll see this. I set-up the
queries for the combo boxes and created a form based on these. This all
looks good. I studied the DateSerial and DatePart functions. These are
definitely essential functions. I tried to study Between and And because I
got an error message: did not enter keyword…expression [Not] between value 1
and value 2. I have no idea what this means. The expression you gave me
appears to be right so I don’t know why I’m getting this error. But then in
the course of working on this. It occurred to me that I don’t really know
what query to insert the expression in the Transdate. Remember, I created
four and then you helped me create another one. Please help me with this
expression and hopefully the rest will fall in place.

Thanks again!

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!cboMo1,1) And
DateSerial(Forms!frmParam!cboY1,Forms!frmParam1!cboMo1 + 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]

 

[BruceM via AccessMonster.com]

I was suggesting one query in place of your four. You can specify non-
contiguous dates using something like this (as I showed in my previous
posting) in the date field criteria, so there is no need of a query for one
month, another for the second month, a union query to wrangle the first two
queries into one, and a fourth one to handle finding the two Sums, which you
can do quite readily in a report. You can do Sum for the two values in a
query too, but if the output is a report I would let the report handle that
task.

Try something like this as the SQL for a single query:

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]
(Between [Month 1 start] And [Month 1 end]) Or
(Between [Month 2 start] And [Month 2 end])
AND tblTrans_Mstr.Group = "ODC"

Or build the query in design view, and use this as the criteria for TransDate.


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

Add "ODC" as the criteria for [Group], if you like.

Note that I added square brackets around Group. Group is a reserved word, so
should not be used for field (or other) names. It's better if you can change
the name of the field, but if not the square brackets should work. For more:
http://allenbrowne.com/AppIssueBadWord.html

The point here is to get the query working as it should. Once that is done
you can work on using a form to specify the parameters, and other such
refinements.

Try using the above query as the Record Source for a report. Group it by
month, and add the Sum expression to the group footer. Your Access 2007 Help
should provide some guidance on this. I don't have Access 2007 here, so
can't be specific. I did find something from another posting, which may help.
I described it in my April 9 posting.

Hi Bruce,
My post seems to be buried but hopefully you’ll see this. I set-up the
queries for the combo boxes and created a form based on these. This all
looks good. I studied the DateSerial and DatePart functions. These are
definitely essential functions. I tried to study Between and And because I
got an error message: did not enter keyword…expression [Not] between value 1
and value 2. I have no idea what this means. The expression you gave me
appears to be right so I don’t know why I’m getting this error. But then in
the course of working on this. It occurred to me that I don’t really know
what query to insert the expression in the Transdate. Remember, I created
four and then you helped me create another one. Please help me with this
expression and hopefully the rest will fall in place.

Thanks again!

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.

[quoted text clipped - 74 lines]

 

[AccessKay via AccessMonster.com]

Yes, I’d much rather create one query than four and use my report for the
rest of the calculations. I knew there had to be a better way. Thanks for
showing this to me. I didn’t know Group was a reserved word so I immediately
changed all my table fields. I now have my query in place and I’ll try to
incorporate the form with it. I sincerely appreciate all of your help with
this.

I was suggesting one query in place of your four. You can specify non-
contiguous dates using something like this (as I showed in my previous
posting) in the date field criteria, so there is no need of a query for one
month, another for the second month, a union query to wrangle the first two
queries into one, and a fourth one to handle finding the two Sums, which you
can do quite readily in a report. You can do Sum for the two values in a
query too, but if the output is a report I would let the report handle that
task.

Try something like this as the SQL for a single query:

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]
(Between [Month 1 start] And [Month 1 end]) Or
(Between [Month 2 start] And [Month 2 end])
AND tblTrans_Mstr.Group = "ODC"

Or build the query in design view, and use this as the criteria for TransDate.

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

Add "ODC" as the criteria for [Group], if you like.

Note that I added square brackets around Group. Group is a reserved word, so
should not be used for field (or other) names. It's better if you can change
the name of the field, but if not the square brackets should work. For more:
http://allenbrowne.com/AppIssueBadWord.html

The point here is to get the query working as it should. Once that is done
you can work on using a form to specify the parameters, and other such
refinements.

Try using the above query as the Record Source for a report. Group it by
month, and add the Sum expression to the group footer. Your Access 2007 Help
should provide some guidance on this. I don't have Access 2007 here, so
can't be specific. I did find something from another posting, which may help.
I described it in my April 9 posting.

Hi Bruce,
My post seems to be buried but hopefully you’ll see this. I set-up the

[quoted text clipped - 16 lines]

 

[BruceM via AccessMonster.com]

Glad to help. Allen Browne has devised a database (it is available at the
link I provided) that can check Access databases for reserved words and other
potential problems. It is easy to use, and quite thorough.

Good luck with the project.

Yes, I’d much rather create one query than four and use my report for the
rest of the calculations. I knew there had to be a better way. Thanks for
showing this to me. I didn’t know Group was a reserved word so I immediately
changed all my table fields. I now have my query in place and I’ll try to
incorporate the form with it. I sincerely appreciate all of your help with
this.

I was suggesting one query in place of your four. You can specify non-
contiguous dates using something like this (as I showed in my previous

[quoted text clipped - 43 lines]