Last 12 mo expression

[Antonio]

Can anyone suggest an expression that would return
records having entry dates within the last 4 months of a
specified date?
Example:
Table Name = tblJCOReview
Table Fields:
ReviewDate EntryDate ReviewTotal
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
Jan 2002 1/1/2002 11
Feb 2002 2/2/2002 11
Mar 2002 3/1/2002 13
Apr 2002 4/1/2002 10
May 2002 5/1/2002 10

I want to use the DSum Function in a form text box to add
up the review totals from the last twelve months from the
entry date displayed for the current record.
So when you have Review Month Jan 2003 displayed on a
form (form displays in "single form" format)...
Review Date EntryDate ReviewTotal txt12MonthTotal
Jan 2003 1/1/2003 10 61
When you have Review Month Feb 2003 displayed on the
form...
Review Date EntryDate ReviewTotal txt12MonthTotal
Feb 2003 2/2/2003 10 62

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.

 

[Antonio]

First sentance should read "Can anyone suggest an
expression that would return >records having entry dates
within the last 12 months of a specified date?"
vice "last 4 months".
TIA

 

[John Vinson]

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.

 

[Guest]

Mr Vinson, I want to thank you for going out of your way
to answer my question. Not only did you provide a
solution to my issue, but also an explaination as to why
what I was attempting had little chance of working. I
really really appreciate the help (as well as all of the
other bits of help I've received from past postings!)

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



.