DSum Glitch

[pixie]

Hi. I'm having problems with the following DSum in my report footer. It gives me #Error when I run the report. I hope someone can help me out as I am at my wits end. What I am trying to do is to create a total budget which is revenues - expenses - expenses in-direct. Those controls are in the detail section of the report as groups. Here is the code.

=DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'")

[PEB]

Hi,

Try to do this:

=nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'"))-nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'"))-nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'"))

:)

Hi. I'm having problems with the following DSum in my report footer. It gives me #Error when I run the report. I hope someone can help me out as I am at my wits end. What I am trying to do is to create a total budget which is revenues - expenses - expenses in-direct. Those controls are in the detail section of the report as groups. Here is the code.

=DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'")

[pixie]

Thanks for the suggestion but that gives me an #Error message too. Any other ideas would be appreciated!

[PEB]

Hey
try separetly
nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'"))

then

nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'"))

and

nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'"))

and see the results!

What kind of result it gives and where?

:)

[pixie]

Thanks again but I am still getting an #error message. I'm using =Sum(IIf) expressions, which work, but really want to figure out what is wrong with the DSum so any other thoughts would be great appreciated. Is there a rating on this site?

[PEB]

So,

qrySummaryRepor t is the name of a query or of your report?

If it is name of Report - so error...

If it is a parametred query - so error...

DSUM works only with tables and queries out of the current record source...

Your solution is better than using Dsum..

About the rating you can express your opinion in Community longe dicussions...


Have a nice day...

:)

[pixie]

Ah ha! It is a 5 parameter query. I never knew that about DSum. Thanks ever so much.