John,
Thanks for the tip which seems to work to a point, but i need help finishing
the criteria portion of the function. I'd like the criteria for one of the
text boxes to be for the current month only and the second to be for the
current year.
With Tina's correction (thanks Tina!) that's precisely what my criterion does.
See below for an explanation.
this is what i'm using for the expression and the domain. i can't get your
suggestion for the criteria to work.
=DSum("[taxablesales]","M_sales","[dateofsales] = xxxxxxx") where xxxxx =
current month or current year.
A DateOfSales value *CANNOT* be equal to a month, or to a year. It can only be
a precise instant of time. Your criterion should not use the = operator to
compare it with a point in time; it needs to use a range (from the first of
the month to today, for example). If DateOfSales falls anywhere within that
*RANGE* then you want to sum it... right?
So: my criterion uses the DateSerial() function to find the first day of the
month, or the year.
MonthToDate: DSum("
", "[tablename]", "[datefield] >= #" &
DateSerial(Year(Date()), Month(Date()), 1)) & "#")
will find and sum all records where the datefield is greater than the date
calculated using the year of today's date (2009), the month of today's date
(February), and the first day of the month. DateSerial takes three arguments,
year, month, and day, and constructs a date/time field from them - in this
case, 2/1/2009.
YearToDateSum("", "[tablename]", "[datefield] >= #" &
DateSerial(Year(Date()), 1, 1)) & "#")
Same logic but uses 1 for the month, to construct 1/1/2009.
If your data may have FUTURE dates that you want to exclude, you can add a
criterion
<= Date()
to include up through midnight last night, or
<= Now()
to include all dates and times up to the second that the query is run.