Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

unbound textbox on a form using Dsum for contol source getting #Error message

Posted on 2007-07-31
10
Medium Priority
?
352 Views
Last Modified: 2008-02-01
I have a unbound txtbox "txtonnetworktotal" that has the control source set to =DSum(("[voice_euro]"+"[sms_euro]"+"[gprs_euro]"),"qrygphursumonnetwork"). The SQL for the query is as follows:

SELECT qrygphursubform.company, Sum(qrygphursubform.voice_euro) AS voice_euro, Sum(qrygphursubform.sms_euro) AS sms_euro, Sum(qrygphursubform.gprs_euro) AS gprs_euro, Sum(qrygphursubform.total_euro) AS total_euro
FROM qrygphursubform
WHERE (((qrygphursubform.newrecord)="Yes") AND ((qrygphursubform.imsi)=[Forms]![frmgphur]![imsi]))
GROUP BY qrygphursubform.company
HAVING (((qrygphursubform.company)="OM" Or (qrygphursubform.company)="BS" Or (qrygphursubform.company)="D2" Or (qrygphursubform.company)="F2" Or (qrygphursubform.company)="VF" Or (qrygphursubform.company)="LT"));

When I bring up the form all the data on the form and subforms are correct. And when I run the query seperatly and type in the imsi field the query works fine. However in the unbound text box on the form I am getting #Error. What is going on with this?
0
Comment
Question by:woodje
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19604470
DSum can only sum one field at a time.  Use this instead:

=DSum("[voice_euro]", "qrygphursumonnetwork") + DSum("[sms_euro]", "qrygphursumonnetwork") + DSum("[gprs_euro]", "qrygphursumonnetwork")
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 19604950
As a single DSum, you would have to manage possible Null values in your fields. This expression should work as well:

=DSum("voice_euro+sms_euro+gprs_euro","qrygphursumonnetwork")

=DSum("Nz(voice_euro)+Nz(sms_euro)+Nz(gprs_euro)","qrygphursumonnetwork")

The second converts all Null values to 0, so that the total is correct.

(°v°)
0
 
LVL 2

Expert Comment

by:tbeernot
ID: 19607005
Personally I would move the sum logic into the form-query and then just show the result. Then you would be able to sum multiple fields at once.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:woodje
ID: 19608127
harfang,

Your first suggestion worked great for the textbox. When I use the same thing for another textbox and just change the query name to "qrygphursumoffnetwork" it does not work. I have enclosed the SQL for that query as well.  

SELECT qrygphursubform.company, Sum(qrygphursubform.voice_euro) AS voice_euro, Sum(qrygphursubform.sms_euro) AS sms_euro, Sum(qrygphursubform.gprs_euro) AS gprs_euro, Sum(qrygphursubform.total_euro) AS total_euro
FROM qrygphursubform
WHERE (((qrygphursubform.newrecord)="Yes") AND ((qrygphursubform.imsi)=[Forms]![frmgphursubform]![imsi]))
GROUP BY qrygphursubform.company
HAVING (((qrygphursubform.company) Not Like (([qrygphursubform].[company])="OM" Or ([qrygphursubform].[company])="BS" Or ([qrygphursubform].[company])="D2" Or ([qrygphursubform].[company])="F2" Or ([qrygphursubform].[company])="VF" Or ([qrygphursubform].[company])="LT")));

any suggestions on this one.
0
 
LVL 58

Expert Comment

by:harfang
ID: 19611924
From what I see, the field names are the same, so you should get something.

Form a general point of view "it does not work" is a rather useless comment. It's much better to describe what you did get: error message, empty cell, wrong result, etc.

When you open the query directly, do you have empty cells? If you have Null values, you really need the second version.

What is total_euro? Isn't that the same? Retrieving a single field is much easier, as Patrick said.

(°v°)
0
 

Author Comment

by:woodje
ID: 19612027
The query is bringing back the data and their are no null values. The fields have 0's in them if they are blank. The txtbox is showing #Error.
0
 
LVL 58

Expert Comment

by:harfang
ID: 19612767
OK. #Error means that the syntax is correct but that DSum encountered a problem. The most likely was the field names, but it can also be a typo in the query name. Did you copy-paste the query name or did you retype it?

You get a more explicit error message from the immediate pane (Ctrl+G). Try:

    ? DSum("voice_euro+sms_euro+gprs_euro","qrygphursumonnetwork")

That should work. Then try:

    ? DSum("voice_euro+sms_euro+gprs_euro","qrygphursumoffnetwork")

Do you get a better error message?
(°v°)
0
 

Author Comment

by:woodje
ID: 19616496
harfang,

I copy and pasted the query name into the Control Source string. And when I place the data you provided in the immediate pane this is the error code I am getting:

Run-time error '2001':
You canceled the previous operation
0
 

Author Comment

by:woodje
ID: 19616539
I found it the problem was that the query had a typo in one of the criteria sections. That is nerve racking. Thanks for all of your help.
0
 
LVL 58

Expert Comment

by:harfang
ID: 19619310
I see. Glad you found it; when using one of the lookup functions, it's of course essential to debug the query first...
Success with your project!
(°v°)
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question