Query Designer Help Please

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

Open in new window


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

Open in new window


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.
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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
SteveL13Author 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
Gustav BrockCIOCommented:
1:
TotalLastYearMonth: Sum(IIf(DateDiff("m",PostDate],[Forms]![frmSelectDateRange]![txtDate])=12,[Amount],0))

Open in new window

2:
TotalLastYearMonth: Sum(IIf(DateDiff("yyyy",PostDate],[Forms]![frmSelectDateRange]![txtDate])=0,[Amount],0))

Open in new window

3:
TotalLastYearMonth: Sum(IIf(DateDiff("yyyy",PostDate],[Forms]![frmSelectDateRange]![txtDate])=1,[Amount],0))

Open in new window

/gustav
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PatHartmanCommented:
LastYearCurrentPeriod: Sum(IIf([PostDate]>= DateAdd("yyyy", -1, [Forms]![frmSelectDateRange]![txtStartDate]) And [PostDate]<= DateAdd("yyyy", -1, [Forms]![frmSelectDateRange]![txtEndDate]),[Amount],0))

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
SteveL13Author Commented:
So far the testing has gone well.  Except, Pat, with your  LastYearTotal I'm getting "wrong number of arguments".
0
PatHartmanCommented:
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
SteveL13Author 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
Gustav BrockCIOCommented:
Why this mess? My original expression worked, so will the next three.

/gustav
0
SteveL13Author 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
PatHartmanCommented:
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
Gustav BrockCIOCommented:
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))

Open in new window


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
SteveL13Author 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
Gustav BrockCIOCommented:
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))

Open in new window

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

Open in new window

/gustav
0
SteveL13Author 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))

Open in new window



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

Open in new window


Note:  The EndDate on the form always populates with the last date of the month after the StartDate is entered.
0
Gustav BrockCIOCommented:
Perhaps you should rephrase the specs, indeed where EndDate now comes in.

/gustav
0
SteveL13Author 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
Gustav BrockCIOCommented:
Then try this:

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

Open in new window

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

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
This was a tough one.  Thank you.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.