Date Function

  • Thread starter Thread starter Goopie
  • Start date Start date
G

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
 
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 & "#)"
 
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
 
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.
 
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
 
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.
 
Back
Top