Dsum totals query and between date parameter not working

[jderrig]

Here is my issue in greater detail. Sorry for not being more specific...I've tried Dsum, Davg, Dlookup and nothing works correctly.



I have a totals query that is I have a DSum field where I want to sum only the field in these column where the salary expense is greater than 0: GrossRev. Since I don't have PersonnelExp for every account listing, it's skewing the true personnel expense as a percent of gross revenue. (I have other fields that I need to sum all the values in the column regardless of zero values)

Totals Query:
GrossRev: DSum("[GrossRevenue]","**Single account ","[Salary/BenefitExp] > 0")


The Totals Query is pulling from another query where I have filtered based on two parameters from a form where I can select the data between two years to give me the results.

**Single Account query:
Between [forms]![frmSingleAcctGraph]![Year 1] And [forms]![frmSingleAcctGraph]![Year2]

The DSum field is not producing the correct calculations.

Attached is a sample db.

When you open frmSingleAcctGraph and select just one year, you get the right GrossRev amount - 94985997 (which is the last column). It's when I select both years 2007 and 2008 from form and the last GrossRev column simply sums the the two years together and repeats the same total for each - 148930091.

The **Graph Output Single account query isn't recognizing the filter from on the **Single account query which is Between [forms]![frmSingleAcctGraph]![Year 1] And [forms]![frmSingleAcctGraph]![Year2]

[June7]

I can't review db files until after Feb 22. If you still need help then, submit post.

Why do you show ** characters in the data source name? It is bad practice to use spaces, special characters, punctuation (underscore is exception) in any names.

[jderrig]

Unfortunately, it's a legacy system and I don't have time to update it...perhaps some day. I haven't found a solution yet so any assistance would be much appreciated. Thanks

[June7]

The acEdit parameter for the OpenQuery is meaningless because query is an aggregate and aggregate queries are by their nature not editable.

The SingleAccount query has #Error for the LocalNetWorth calc in two records due to division by 0.


Problem is the GrossRev figure is a DSum expression that sums the records regardless of the year. Use:
GrossRev: DSum("[GrossRevenue]","**Single account ","[Salary/BenefitExp] > 0 AND [Year]=" & [Year])

or change the Total row to Sum and the expression to:
GrossRev: IIf([Salary/BenefitExp]>0,[GrossRevenue],0)


Year is a reserved word, advise not to use reserved words as names.

[jderrig]

Thanks, I tried both and it still totals the entire gross revenue column instead of only the totals of gross revenue where salary is greater than 0.

[June7]

The edits worked for me.

Wait, need to put the query name within [] because of the spaces and special characters and you have an extraneous space at end of the query name. I am surprised anything returns with that syntax.

[jderrig]

Thank you, that seemed to work.