Date Function

[Goopie]

Im currently using this:
=DSum("Cost","Stock"," DOD Between #01/07/08# And #31/12/08#")
I'd like to substitute the 08 to obtain the current year, as well as
calculate the year previous, into this expression. In other words I want the
above expression to provide between 01/07/current year and 31/12/current year
as well as the same dates for the previous year.
I want the expression to work for the control source of a txtbox and as a
query expression. That way I don't have to manually update it each year.
Thanks for any help

 

[Allen Browne]

Firstly, JET expects you to use US date formats in criteria, not your local
date format. For the 2nd half of this year, it should be:
=DSum("Cost","Stock","DOD Between #07/01/08# And #12/31/08#")

Explanation in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

You will end up with something like this:
"(DOD Between #7/1/" & Year(Date()) & "# And #12/31/" & Year(Date()) &
"#) OR DOD Between #7/1/" & Year(Date()) - 1 & "# And #12/31/" &
Year(Date()) - 1 & "#)"

 

[John Spencer]

Current Year

DSUM("Cost","Stock","DOD Between " &
Format(DateSerial(Year(Date()),1,1),"#yyyy-mm-dd#") & " AND " &
Format(DateSerial(Year(Date()),12,31),"#yyyy-mm-dd#")

Prior Year
DSUM("Cost","Stock","DOD Between " &
Format(DateSerial(Year(Date())-1,1,1),"#yyyy-mm-dd#") & " AND " &
Format(DateSerial(Year(Date())-1,12,31),"#yyyy-mm-dd#")

Prior Year and Current Year
DSUM("Cost","Stock","DOD Between " &
Format(DateSerial(Year(Date())-1,1,1),"#yyyy-mm-dd#") & " AND " &
Format(DateSerial(Year(Date()),12,31),"#yyyy-mm-dd#")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Goopie]

Thank you to Allen and John for your replies.

I'm having trouble with the syntax for both of your solutions.

I'm using this with Allen's:
=DSum("Cost","Stock","(DOD Between #7/1/" & Year(Date()) & "# And #12/31/" &
Year(Date()) and John's as quoted in his reply.

Both examples give me errors (missing a closing parenthesis, bracket or bar).

Can you help with the correct syntax?

Thank you again.

 

[John Spencer]

My error, I forgot to include the escape slash before the # signs.

DSUM("Cost","Stock","DOD Between " &
Format(DateSerial(Year(Date()),1,1),"\#yyyy-mm-dd\#") & " AND " &
Format(DateSerial(Year(Date()),12,31),"\#yyyy-mm-dd\#")

Prior Year
DSUM("Cost","Stock","DOD Between " &
Format(DateSerial(Year(Date())-1,1,1),"\#yyyy-mm-dd\#") & " AND " &
Format(DateSerial(Year(Date())-1,12,31),"\#yyyy-mm-dd\#")

Prior Year and Current Year
DSUM("Cost","Stock","DOD Between " &
Format(DateSerial(Year(Date())-1,1,1),"\#yyyy-mm-dd\#") & " AND " &
Format(DateSerial(Year(Date()),12,31),"\#yyyy-mm-dd\#")



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Goopie]

Thanks again John, that solved the problem, although I needed another end
bracket thus:

DSUM("Cost","Stock","DOD Between " &
Format(DateSerial(Year(Date()),1,1),"\#yyyy-mm-dd\#") & " AND " &
Format(DateSerial(Year(Date()),12,31),"\#yyyy-mm-dd\#"))

Appreciate your help with this.