By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

DSum Glitch

P: 41
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]","qrySummaryReport","[Type] = 'Revenues'")-DSum("[Sum of Total Budget]","qrySummaryReport","[Type] = 'Expenses In-direct'")-DSum("[Sum of Total Budget]","qrySummaryReport","[Type] = 'Expenses'")
Sep 15 '06 #1
Share this Question
Share on Google+
6 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,

Try to do this:

=nz(DSum("[Sum of Total Budget]","qrySummaryReport","[Type] = 'Revenues'"))-nz(DSum("[Sum of Total Budget]","qrySummaryReport","[Type] = 'Expenses In-direct'"))-nz(DSum("[Sum of Total Budget]","qrySummaryReport","[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]","qrySummaryReport","[Type] = 'Revenues'")-DSum("[Sum of Total Budget]","qrySummaryReport","[Type] = 'Expenses In-direct'")-DSum("[Sum of Total Budget]","qrySummaryReport","[Type] = 'Expenses'")
Sep 16 '06 #2

P: 41
Thanks for the suggestion but that gives me an #Error message too. Any other ideas would be appreciated!
Sep 18 '06 #3

PEB
Expert 100+
P: 1,418
PEB
Hey
try separetly
nz(DSum("[Sum of Total Budget]","qrySummaryReport","[Type] = 'Revenues'"))

then

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

and

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

and see the results!

What kind of result it gives and where?

:)
Sep 19 '06 #4

P: 41
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?
Sep 19 '06 #5

PEB
Expert 100+
P: 1,418
PEB
So,

qrySummaryReport 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...

:)
Sep 20 '06 #6

P: 41
Ah ha! It is a 5 parameter query. I never knew that about DSum. Thanks ever so much.
Sep 20 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.