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!
9  5434 
not really sure but how can[acct] be a value on a textbox?
I thought you were supposed to put text boxes in square brackets. Could that be my problem?
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] &"'")
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!
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)
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.
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?
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.
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!
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Mark Reed |
last post by:
All,
I have never used this function before and am not sure it what I need.
Just to clarify, I have a report based on a query which has amoungst other
field, wk, parea & packs_req. What I am trying to do is create a subreport
for the main report which gives a generalised breakdown of the main report.
For the gender of 'Ladies', there may be several parea's so I want to sum
the packs_req for ladies only. (Gender is not a field I can use,...
|
by: John Baker |
last post by:
Hi:
At the bottom of a report, I wish to sum all the amounts that appear in a field called
Discount$ that appears on the report. I am not doing this summing in the query that
supports the report because I have sprinkled the discounts amount into every record, and
it only applies in the totals summary for each invoice.
In any case, the Field is called Discount$ and the report is called RINV1Q1. How do I
reference the field in the report...
|
by: stephena |
last post by:
Hello all, hope someone can help.
I recently built a report showing the distribution of money in and out
of the company. Simplified version of which looks like:
DECEMBER 2004
TELEPHONE SALES: INTERNET: TOTAL:
£1200 £1000 £2200
I then built a sub-report showing adjustments, eg:
|
by: Marc Aube |
last post by:
Is there a web site that can ofer some help. The quotation marks are posing
an issue as well as other items for this function. I have some books but
they are not consistent in their use of the code lingo. Thank you for any
help. Presently I have tables and forms to gather employee info. I use the
subform to gather status of absence, text field. What I want to do is
calculate the vacation hours and subtract them from the total alocation of...
|
by: pixie |
last post by:
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("","qrySummaryReport"," = 'Revenues'")-DSum("","qrySummaryReport"," = 'Expenses...
| | |
by: ckpoll2 |
last post by:
Hi,
I have made a report that has a query as its record source. I put a text box in there that I want to run a dsum function off of a different query. The criteria that I need to use is that it returns the appropriate number for whatever name appears in the Name text box within the Name Header. Here's the formula I have:
=DSum("","Overtime","Name=")
All it's giving me is the sum for everyones hours in the overtime query in each...
|
by: danielgoss |
last post by:
Hi
I have a report that has loads of textboxes that calculate things based on the value on another textbox in the report. I have put a hidden textbox on my report that gets its value from an inputbox when the report opens. I then want to use the value inputted in a DSUM calculation on the same form.
The hidden textbox is called tbHolddate, its control source is:
=InputBox()
The textbox that calculates a value using a DSUM control...
|
by: ringer |
last post by:
Hi,
I have a text box on report where I need to have dsum return a total.
The records I need the total from are not in the table that is the report's recordsource, and to complicate things further, I need to use a variable that is derived from behind the report in the criteria for the dsum.
When I try to see print preview, this version of the statement errors before opening saying "Access cannot find the field 'SavingsPeriodEndDate'...
|
by: Adam1331 |
last post by:
I've been trying to get the DSum function to work but I've been running into some roadblocks. What I'm trying to do is to total up times in a timesheet report into different departments. The report is based on a query that gathers all an employee's info. In that query, I have a calculated field which gives an employee's total time on a process.
(DateDiff("n",,))/60
This gives the total time in hourly fractions. Now what I'm trying to do...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
