Dsum Syntax

[CharlieT302]

Hi,

I am using the following expression in an unbound object on a form. I know my criteria has an incorrect syntax, but I am unclear as how to fix it.

=DSum("[purchase]","qry_Schedule",Format([deldate],"yyyy")=Format(Date(),"yyyy"))

I have also tried (among other things):
=DSum("[purchase]","qry_Schedule",Format([deldate],"yyyy")=#"&Format(Date(),"yyyy")&"#")

I basically wish to summarize the purchases only for the current year. How to fix?

 

[PHV]

Like this ?
=DSum("[purchase]","qry_Schedule","Year([deldate])=Year(Date())")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

thanks PHV,

I just tried your formula, which is sort of a variation on my original. Niether formula is rejected, but for some reason they are totaling for the current year.

Actually, the formula is calculating the entire record set and ignoring the criteria. Not sure why ??? I keep thinking it is a syntax error, but I am not seeing it.

 

[PHV]

which is sort of a variation on my original
I disagree as yours has severe quoting problem ...

 

[CharlieT302]

Conceptually was my meaning. As I said, there must be a syntax problem.

I have been trying many variations; the last being your suggestion. I can get aggregate totals, but cannot get the criteria to be recognized.

 

[dhookom]

From my view of your results, it looks like PH's suggestion works great. I don't see any errors ;-)

Try open a new query and view the SQL. Enter

SELECT Sum([purchase]) FROM qry_Schedule WHERE Year([deldate])=Year(Date());

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]