A
Antonio
Mr Vinson provided the following expression which allows
me to post a rolling 12 month total based on the
current "ReviewDate" specified on the current record
shown...and it worked wonderfully.
=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate]
Between #" & [EntryDate] & "# And DateAdd(""M"", -12, #"
& [EntryDate] & "#)")
On the same form I need to have 4 text boxes giving a
quarterly average (1st Quarter, 2nd Quarter, 3rd Quarter,
4th Quarter)of the review totals based on the date
(Review Date) currently displayed on the record. I
thought specifying these dates would work the same way as
the expression Mr Vinson provided by substituting
the ""W"" for a ""Q"", and a -1 for the 12 for the first
quarter, a -2 for the second quarter, a -3 for the third
quarter, and a -4 for the fourth quarter...but it doesnt
seem to be working out (this was previously created on an
Excel Spread sheet and I am comparing the totals)
Is it because the ""Q"" I am replacing is looking at the
current Quarter...and what I am asking for isnt really
a "quarterly average at all"? Rather what I have in mind
are 4 text boxes that go back and gets an average total
in quarterly segments based on the date specified on my
form??
Example:
ReviewMonth EntryDate ReviewTotal
Nov 2002 11/1/2002 11
Dec 2002 12/1/2002 9
Jan 2003 1/1/2003 10
Feb 2003 2/1/2003 10
Mar 2003 3/1/2003 10
Apr 2003 4/1/2003 10
May 2003 5/1/2003 10
Jun 2003 1/1/2003 11
Jul 2003 2/2/2003 11
Aug 2003 3/1/2003 13
Sep 2003 4/1/2003 10
Oct 2003 5/1/2003 10
Nov 2003 11/1/2003 12
Dec 2003 12/1/2003 11
If you have a record displayed on a form with a Review
Month of Nov 2003 then...
4th Quarter Avg = 32/3 (Sep - Nov 03)
3rd Quarter Avg = 35/3 (Jun - Aug 03)
2nd Quarter Avg = 32/3 (Mar - Apr 03)
1st Quarter Avg = 29/3 (Dec 2002, Jan - Feb 03
Thanks in Advance
me to post a rolling 12 month total based on the
current "ReviewDate" specified on the current record
shown...and it worked wonderfully.
=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate]
Between #" & [EntryDate] & "# And DateAdd(""M"", -12, #"
& [EntryDate] & "#)")
On the same form I need to have 4 text boxes giving a
quarterly average (1st Quarter, 2nd Quarter, 3rd Quarter,
4th Quarter)of the review totals based on the date
(Review Date) currently displayed on the record. I
thought specifying these dates would work the same way as
the expression Mr Vinson provided by substituting
the ""W"" for a ""Q"", and a -1 for the 12 for the first
quarter, a -2 for the second quarter, a -3 for the third
quarter, and a -4 for the fourth quarter...but it doesnt
seem to be working out (this was previously created on an
Excel Spread sheet and I am comparing the totals)
Is it because the ""Q"" I am replacing is looking at the
current Quarter...and what I am asking for isnt really
a "quarterly average at all"? Rather what I have in mind
are 4 text boxes that go back and gets an average total
in quarterly segments based on the date specified on my
form??
Example:
ReviewMonth EntryDate ReviewTotal
Nov 2002 11/1/2002 11
Dec 2002 12/1/2002 9
Jan 2003 1/1/2003 10
Feb 2003 2/1/2003 10
Mar 2003 3/1/2003 10
Apr 2003 4/1/2003 10
May 2003 5/1/2003 10
Jun 2003 1/1/2003 11
Jul 2003 2/2/2003 11
Aug 2003 3/1/2003 13
Sep 2003 4/1/2003 10
Oct 2003 5/1/2003 10
Nov 2003 11/1/2003 12
Dec 2003 12/1/2003 11
If you have a record displayed on a form with a Review
Month of Nov 2003 then...
4th Quarter Avg = 32/3 (Sep - Nov 03)
3rd Quarter Avg = 35/3 (Jun - Aug 03)
2nd Quarter Avg = 32/3 (Mar - Apr 03)
1st Quarter Avg = 29/3 (Dec 2002, Jan - Feb 03
Thanks in Advance
-----Original Message-----
I tried to use the following expression:
=DSum("[ReviewTotal]","[tblJCOReview]","[EntryDate]=
Between [EntryDate] And DateDiff("M", 12, [EntryDate])")
Access tells me invalid syntax...but I cant find any
examples of an expression that can be placed in the last
section of the DSum function to return the last 12 months
based on a date field currently displayed.
Please help.
Well, DateDiff returns the number of months between two dates; you
want DateAdd instead. And if you're using the BETWEEN operator you
should not be using the = operator too - just one operator needed!
More subtly, you need two consecutive " marks around the M, because
the entire string is delimited with " marks; and you need to pull the
EntryDate you're using as a criterion out of the string, and add # as
a date delimiter.
Try
=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate] Between #" &
[EntryDate] & "# And DateAdd(""M"", -12, #" & [EntryDate] & "#)")
If the current record has EntryDate of 12/1/03, this will evaluate the
search criterion to
[EntryDate] BETWEEN #12/1/03# AND #12/1/02#
which should work correctly.
.