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?
 
Gustav BrockConnect With a Mentor 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))

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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
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
 
SteveL13Author Commented:
This was a tough one.  Thank you.
0
 
Gustav BrockCIOCommented:
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.

All Courses

From novice to tech pro — start learning today.