DSum function in a report

[BlackJack17]

Alright you all have been so helpful with everything else that I thought I'd run one more issue by you. I have put together a report that is driven by a query. This query (Unique Records) returns all unique records in a table (DDRS Data). Each unique record is a string of elements from the table (15 different ones total) and I'm trying to get the report to sum the multiple dollar amounts that appear for each unique record. I've used the DSum function:

=DSum("Trial Balance Amount","DDRS Data","Acct = [Acct]")

where Trial Balance Amount is the field that I need to sum, DDRS Data is the table from which the dollar amounts are pulled, Acct is the field in the table, and [Acct] is the text box in the report. I keep getting a #Error message. Any idea what's wrong? Is there any more information that I can give you that would help? Thanks so much!

[TSIGOS1]

not really sure but how can[acct] be a value on a textbox?

[BlackJack17]

I thought you were supposed to put text boxes in square brackets. Could that be my problem?

[mlcampeau]

Alright you all have been so helpful with everything else that I thought I'd run one more issue by you. I have put together a report that is driven by a query. This query (Unique Records) returns all unique records in a table (DDRS Data). Each unique record is a string of elements from the table (15 different ones total) and I'm trying to get the report to sum the multiple dollar amounts that appear for each unique record. I've used the DSum function:

=DSum("Trial Balance Amount","DDRS Data","Acct = [Acct]")

where Trial Balance Amount is the field that I need to sum, DDRS Data is the table from which the dollar amounts are pulled, Acct is the field in the table, and [Acct] is the text box in the report. I keep getting a #Error message. Any idea what's wrong? Is there any more information that I can give you that would help? Thanks so much!

I'm no expert on the syntax for DSum but yours definitely doesn't look right.
Try something like:
=DSum("[Trial Balance Amount]","DDRS Data", "[Acct] = "&[Acct])

If Acct isn't a number then try:
=DSum("[Trial Balance Amount]","DDRS Data", "[Acct] = '" & [Acct] &"'")

[BlackJack17]

Great, that worked perfectly. I have one other follow-on issue...

I need to add additional elements to the formula to include fields like [Program Description] and [Fed], among others. Here's the formula that I put together to include [Program Description]:

=DSum("[Trial Balance Amount]","DDRS Data","[Acct] = " & [Acct] And "[Program Description) = " & [Program Description])

but it didn't work. What is wrong with the formula?

Thanks again!

[mlcampeau]

Great, that worked perfectly. I have one other follow-on issue...

I need to add additional elements to the formula to include fields like [Program Description] and [Fed], among others. Here's the formula that I put together to include [Program Description]:

=DSum("[Trial Balance Amount]","DDRS Data","[Acct] = " & [Acct] And "[Program Description) = " & [Program Description])

but it didn't work. What is wrong with the formula?

Thanks again!

The trickiest part to this is knowing how/when to use single/double quotes. I'm still learning so I'm going to take a stab at this:

=DSum("[Trial Balance Amount]","DDRS Data", "[Acct] = " & [Acct] & " AND [Program Description]= '" & [Program Description] & "'")

(I'm assuming Program Description is a string)

[BlackJack17]

You all are amazing. A new problem surfaced...

As I tried putting all 15 fields in the formula, I ran into the 2048 limit. Is there any way around this?

I swear I'm almost done with this thread.

[mlcampeau]

You all are amazing. A new problem surfaced...

As I tried putting all 15 fields in the formula, I ran into the 2048 limit. Is there any way around this?

I swear I'm almost done with this thread.

To get the results you want you need to make sure that 15 fields match?? I've only used DSum with a maximum of 3 or 4 fields, so unfortunately, I don't know a way to get around this (I'm still learning a lot when it comes to Access). I also don't know what the 2048 limit is, but I'm assuming it's just not liking having 15 fields. Can you not sum the fields in your query rather than using DSum?

[BlackJack17]

Yeah, I unfortunately need to sum on all 15 fields. It has to do with a line of accounting which is comprised of 15 different elements, all of which I need the total for each unique line.

As for the 2048, I'm under the impression that a formula in a text box can only be 2048 characters long. Is this correct?

As for summing in the query, I can't seem to find a way to do it. The query is based on table DDRS Data and narrows it down to all the unique lines. (Like 21-X... would be different than 21-Z...) It finds each unique line regardless of how many times it appears and lists it only once. The query is just all the fields from the table excluding the dollar amount fields. Is there a way to link it so that query Unique Records finds the sums of the lines in table DDRS Data?

If not, is there anyone out there who knows how I can get all 15 elements in my DSum formula from the previous posts?

Thanks for your continued help.

[mlcampeau]

Yeah, I unfortunately need to sum on all 15 fields. It has to do with a line of accounting which is comprised of 15 different elements, all of which I need the total for each unique line.

As for the 2048, I'm under the impression that a formula in a text box can only be 2048 characters long. Is this correct?

As for summing in the query, I can't seem to find a way to do it. The query is based on table DDRS Data and narrows it down to all the unique lines. (Like 21-X... would be different than 21-Z...) It finds each unique line regardless of how many times it appears and lists it only once. The query is just all the fields from the table excluding the dollar amount fields. Is there a way to link it so that query Unique Records finds the sums of the lines in table DDRS Data?

If not, is there anyone out there who knows how I can get all 15 elements in my DSum formula from the previous posts?

Thanks for your continued help.

Why don't you create a separate query to get the sum that you want, and then you can use DLookup()? Again, I must point out that I am still learning Access and there may be an easier/more efficient way of doing this...I just don't know of one!