DSum problem 1

[cjany]

I have a query with a parameter prompting for a date range,
(Between [Enter 1st date] and [Enter last date]). In the report which pulls data from this query, I need 2 total fields which pull the totals of an assessment amount for each type of account.

CourtCosts: totalassessment
(=DSum("[ADJASSD]","qrytest2","[ACCTNO]=20000000"))

Fines: Totalassessment
(=DSum("[ADJASSD]","qrytest2","[ACCTNO]=30000000"))

I'm getting errors in these fields, but if I use a particular date range in the query, "between 08/01/2006 and 08/31/2006", then I get the correct data in the reports total fields.

Any suggestions???

 

[dhookom]

It's difficult to understand exactly what's happening. Is qrytest2 the record source of the report? Does qrytest2 have the parameter prompts? Are these expressions in control sources or in the query? Is the AcctNo field really numeric or is it text?

Are you simply attempting to sum a field from records in the report that match a specific condition? If so, you should be able to use:

=Sum(Abs([ACCTNO]=20000000) * [ADJASSD])



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]

 

[cjany]

Thank you. That seemed to have worked, but I don't understand this particular function. Could you please explain this code?

 

[Golom]

[blue]Abs([ACCTNO]=20000000)[/blue]
[ACCTNO]=20000000 compares [ACCTNO] to 20000000 and returns TRUE(-1) of they are equal or FALSE(0) if they are not. The Abs function just converts the -1 values to +1.

[blue]Abs([ACCTNO]=20000000) * [ADJASSD][/blue]
Multiplies [ADJASSD] by 1 when [ACCTNO] = 20000000 and by zero when it isn't.

[blue]Sum(Abs([ACCTNO]=20000000) * [ADJASSD])[/blue]
Then adds up the values computed. Only the [ACCTNO] = 20000000 entries will be included because all others were multiplied by zero.

 

[cjany]

Thank you for the explanation, I would never have figured that out.

 

[dhookom]

Thanks for cleaning up my reply Golom ;-)

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]

 

[Golom]

Duane

It was your inspired code. I wouldn't have thought of that on my own.

Nice work! Have a star!