sum if...

[lis]

table with a date field in dd/mm/yyyy format and a field (call if field B)
with a value in it. i'd like to display on a form the summed value of field B
for the current month and for the year to date sum. For example the form
will read:

February : $15,000
2009 : $25,345

thanks in advance,

 

[John W. Vinson]

table with a date field in dd/mm/yyyy format and a field (call if field B)
with a value in it. i'd like to display on a form the summed value of field B
for the current month and for the year to date sum. For example the form
will read:

February : $15,000
2009 : $25,345

thanks in advance,

SumIf is an Excel function, not an Access one. You can use DSum() to sum up
the values in a field; the syntax will depend on the structure of your table,
which we can't see from here, but it would be something like

MonthToDate: DSum("", "[tablename]", "[datefield] > #" &
DateSerial(Year(Date()), Month(Date()), 1)) & "#")
YearToDateSum("", "[tablename]", "[datefield] > #" &
DateSerial(Year(Date()), 1, 1)) & "#")

 

[lis]

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.

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.

thanks in advance

table with a date field in dd/mm/yyyy format and a field (call if field B)
with a value in it. i'd like to display on a form the summed value of field B
for the current month and for the year to date sum. For example the form
will read:

February : $15,000
2009 : $25,345

thanks in advance,

SumIf is an Excel function, not an Access one. You can use DSum() to sum up
the values in a field; the syntax will depend on the structure of your table,
which we can't see from here, but it would be something like

MonthToDate: DSum("", "[tablename]", "[datefield] > #" &
DateSerial(Year(Date()), Month(Date()), 1)) & "#")
YearToDateSum("", "[tablename]", "[datefield] > #" &
DateSerial(Year(Date()), 1, 1)) & "#")

 

[tina]

John, PMFJI, but wouldn't you want to include the first day of the month,
and first day of the year, as

MonthToDate: DSum("", "[tablename]", "[datefield] >= ...
YearToDateSum("", "[tablename]", "[datefield] >= ...

hth

table with a date field in dd/mm/yyyy format and a field (call if field B)
with a value in it. i'd like to display on a form the summed value of field B
for the current month and for the year to date sum. For example the form
will read:

February : $15,000
2009 : $25,345

thanks in advance,

SumIf is an Excel function, not an Access one. You can use DSum() to sum up
the values in a field; the syntax will depend on the structure of your table,
which we can't see from here, but it would be something like

MonthToDate: DSum("", "[tablename]", "[datefield] > #" &
DateSerial(Year(Date()), Month(Date()), 1)) & "#")
YearToDateSum("", "[tablename]", "[datefield] > #" &
DateSerial(Year(Date()), 1, 1)) & "#")

 

[John W. Vinson]

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.