An expert helped me with code to be used in query designer for an expression that is using a date range.  Here was the suggested code:

``````Total: =Sum(IIf([EntryDate]>=#1/1/2017# And [EntryDate]<=#10/31/2017#,[Amount],0))
``````

Which I revised to use a form's start and end dates like:

``````TotalCurrentPeriod: Sum(IIf([PostDate]>=[Forms]![frmSelectDateRange]![txtStartDate] And [PostDate]<=[Forms]![frmSelectDateRange]![txtEndDate],[Amount],0))
``````

Note:  The dates will always represent an entire month no matter how many days there are in the month.

But now I an faced with a need to add three more expressions.  They are:

1)  Same time period one year ago.  (so if the start date entered in the form was 10/1/2017 I need the entire month one year ago.)

2)  Current Year to Date time period  (so if the start date entered in the form was 10/1/2017 I need the entire 2017 year in the criteria.)

3)  Last Year's Total.   (so if the start date entered in the form was 10/1/2017 I need the entire 2016 year in the criteria.)

What would the code be for the three expressions?  Any help much appreciated.
Who is Participating?

CIOCommented:
Then try this:

2:
``````TotalThisYearMonth: Sum(IIf(DateDiff("m",[Forms]![frmSelectDateRange]![txtStartDate],[PostDate])>=0 And DateDiff("m",[PostDate],[Forms]![frmSelectDateRange]![txtEndDate])<=0,[Amount],0))
``````
3:
``````TotalThisYearMonth: Sum(IIf(DateDiff("m",[Forms]![frmSelectDateRange]![txtStartDate],[PostDate])>=-12 And DateDiff("m",[PostDate],[Forms]![frmSelectDateRange]![txtEndDate])<=-12,[Amount],0))
``````
/gustav
0

Commented:
If this is the selection criteria for the query, it belongs in the where clause:

Select ... From ...
WHERE [PostDate]>=[Forms]![frmSelectDateRange]![txtStartDate] And [PostDate]<=[Forms]![frmSelectDateRange]![txtEndDate]

If you need more criteria, you can add it easily.

WHERE [PostDate]>=[Forms]![frmSelectDateRange]![txtStartDate] And [PostDate]<=[Forms]![frmSelectDateRange]![txtEndDate]  And SomeField = Forms!frmSelectDateRange!txtSomeField

If you need to use the OR operator, please separate the expression using parentheses so that the AND and OR clauses are evaluated as you need them to be;

a or (b and c)

is different from

(a or b) and c
0

Author Commented:
Pat, I'm sorry I'm not following.  I don't want to add criteria.  I want to add three more expressions (columns I guess).  They are:

1)  Same time period one year ago.  (so if the start date entered in the form was 10/1/2017 I need the entire month one year ago.)

2)  Current Year to Date time period  (so if the start date entered in the form was 10/1/2017 I need the entire 2017 year in the criteria.)

3)  Last Year's Total.   (so if the start date entered in the form was 10/1/2017 I need the entire 2016 year in the criteria.)
0

CIOCommented:
1:
``````TotalLastYearMonth: Sum(IIf(DateDiff("m",PostDate],[Forms]![frmSelectDateRange]![txtDate])=12,[Amount],0))
``````
2:
``````TotalLastYearMonth: Sum(IIf(DateDiff("yyyy",PostDate],[Forms]![frmSelectDateRange]![txtDate])=0,[Amount],0))
``````
3:
``````TotalLastYearMonth: Sum(IIf(DateDiff("yyyy",PostDate],[Forms]![frmSelectDateRange]![txtDate])=1,[Amount],0))
``````
/gustav
0

Commented:

CurrentYearTotal:(Sum(Iff(Year(PostDate) = Year(Forms!frmSelectDateRange!txtStartDate)), [Amount],0)

LastYearTotal:(Sum(Iff(Year(PostDate) = Year(Forms!frmSelectDateRange!txtStartDate) - 1), [Amount],0)

I didn't test this.  Make sure the first one works for February - before and after a leap year.  You may need to use DateSerial() to calculate the two dates.
0

Author Commented:
So far the testing has gone well.  Except, Pat, with your  LastYearTotal I'm getting "wrong number of arguments".
0

Commented:
It isn't easy to write air code.  I see at least one typo but it is in two expressions and a couple of misplaced parentheses.

CurrentYearTotal:Sum(IIF(Year(PostDate) = Year(Forms!frmSelectDateRange!txtStartDate), [Amount],0)

LastYearTotal:Sum(IIF(Year(PostDate) = Year(Forms!frmSelectDateRange!txtStartDate) - 1), [Amount],0)

Both expressions are pretty simple.  If you still don't see the error, try building the expression yourself using the builder.  It will help get the parentheses in the right spots.
0

Author Commented:
I corrected to...

CurrentYearTotal: Sum(IIf(Year([PostDate])=Year([Forms]![frmSelectDateRange]![txtStartDate]),[Amount],0))

and

LastYearTotal: Sum(IIf(Year([PostDate])=Year([Forms]![frmSelectDateRange]![txtStartDate])-1,[Amount],0))

I am now getting the same results with Gus and Pat solutions.
0

CIOCommented:
Why this mess? My original expression worked, so will the next three.

/gustav
0

Author Commented:
I was only stating that the two sets or solutions results matched.  I felt it important to test all recommendations and form there evaluate the most logical ones.
0

Commented:
Be careful when using DateDiff() this way.  You should probably use abs() to avoid the problem seen below:

print DateDiff("m",#11/26/16#,#11/1/17#)
12
print DateDiff("m",#11/1/17#,#11/26/16#)
-12

print abs(DateDiff("m",#11/1/17#,#11/26/16#))
12
0

CIOCommented:
But Pat, those expressions are fictitious and not used here.
Here it is:

``````TotalLastYearMonth: Sum(IIf(DateDiff("m",PostDate],[Forms]![frmSelectDateRange]![txtDate])=12,[Amount],0))
``````

which correctly will return dates of 12 months past of the month of the date entered - and only these.

Applying Abs would also return dates of 12 months in the future which, first, are not very likely to be present, second, will be incorrect.

/gustav
0

Author Commented:
Gus,  I am so sorry I had written the wrong directions in #2 and #3 above.  Here is the correction if you could please help.

2)  Current Year to Date time period  (so if the start date entered in the form was 10/1/2017 I need the entire 2017 year in the criteria.)

Should have said, "So if the start date entered in the form was 10/1/2017 I need the entire 2017 year from the forms START DATE THROUGH THE END OF THE MONTH ENTERED IN THE START DATE.  So if I entered 10/1/2017 in the start date, I need the results to show 10/1/2017 through 10/31/2017.

3)  Last Year's Total.   (so if the start date entered in the form was 10/1/2017 I need the entire 2016 year in the criteria.)

Should have said, "So if the start date entered in the form was 10/1/2017 I need the entire 2016 year from the forms START DATE THROUGH THE END OF THE MONTH ENTERED IN THE START DATE.  So if I entered 10/1/2017 in the start date, I need the results to show 10/1/2016 through 10/31/2016.
0

CIOCommented:
OK. Then it could be:

2:
``````TotalThisYearMonth: Sum(IIf(DateDiff("d",[Forms]![frmSelectDateRange]![txtStartDate],[PostDate])>=0 And DateDiff("m",[Forms]![frmSelectDateRange]![txtDate],[PostDate])=0,[Amount],0))
``````
3:
``````TotalLastYearMonth: Sum(IIf(DateDiff("d",[Forms]![frmSelectDateRange]![txtStartDate],DateAdd("yyyy",1,[PostDate]))>=0 And DateDiff("m",[Forms]![frmSelectDateRange]![txtDate],[PostDate])=-12,[Amount],0))
``````
/gustav
0

Author Commented:
Gus,  Now I'm getting the same results for those two as I am for the first two.  Here are all four of the expressions.

The first two are working perfectly.  To replay what the 3rd and 4th are to be doing:

For the 3rd one:

If the start date entered in the form was 10/1/2017 I need the entire 2017 year from the forms START DATE THROUGH THE END OF THE MONTH ENTERED IN THE START DATE.  So if I entered 10/1/2017 in the start date, I need the results to show 10/1/2017 through 10/31/2017.

And here's my current code:

``````YearToDate: Sum(IIf(DateDiff("d",[Forms]![frmReportingSelector]![txtStartDate],[PostDate])>=0 And DateDiff("m",[Forms]![frmReportingSelector]![txtEndDate],[PostDate])=0,[Amount],0))
``````

Then for the 4th one:

If the start date entered in the form was 10/1/2017 I need the entire 2016 year from the forms START DATE THROUGH THE END OF THE MONTH ENTERED IN THE START DATE.  So if I entered 10/1/2017 in the start date, I need the results to show 10/1/2016 through 10/31/2016.

And here's my current code:

``````YearToDateLastYear: Sum(IIf(DateDiff("d",[Forms]![frmReportingSelector]![txtStartDate],DateAdd("yyyy",1,[PostDate]))>=0 And DateDiff("m",[Forms]![frmReportingSelector]![txtEndDate],[PostDate])=-12,[Amount],0))
``````

Note:  The EndDate on the form always populates with the last date of the month after the StartDate is entered.
0

CIOCommented:
Perhaps you should rephrase the specs, indeed where EndDate now comes in.

/gustav
0

Author Commented:
Here you go:

For the 3rd one:

If the start date entered in the form was 10/1/2017 I need the entire 2017 year from the forms START DATE THROUGH THE END OF THE MONTH ENTERED IN THE START DATE WHICH IS THE ENDDATE (txtEndDate) on the form.  So if I entered 10/1/2017 in the start date, I need the results to show 10/1/2017 through 10/31/2017.

And for the 4th one:

If the start date entered in the form was 10/1/2017 I need the entire 2016 year from the forms START DATE THROUGH THE END OF THE MONTH ENTERED IN THE START DATE WHICH IS THE ENDDATE (txtEndDate) on the form.  So if I entered 10/1/2017 in the start date, I need the results to show 10/1/2016 through 10/31/2016.
0

Author Commented:
This was a tough one.  Thank you.
0

CIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.