Help with IIF expression

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!


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 AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

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 mo
http://allenbrowne.com/Ap****ueBadWord.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.

AccessKay wrote:
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]
Many thanks,
Kay

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

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.


BruceM wrote:
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 mo
http://allenbrowne.com/Ap****ueBadWord.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]
Many thanks,
Kay

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

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.

AccessKay wrote:
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]
Many thanks,
Kay

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