woodje
asked on
unbound textbox on a form using Dsum for contol source getting #Error message
I have a unbound txtbox "txtonnetworktotal" that has the control source set to =DSum(("[voice_euro]"+"[sm s_euro]"+" [gprs_euro ]"),"qrygp hursumonne twork"). The SQL for the query is as follows:
SELECT qrygphursubform.company, Sum(qrygphursubform.voice_ euro) AS voice_euro, Sum(qrygphursubform.sms_eu ro) AS sms_euro, Sum(qrygphursubform.gprs_e uro) AS gprs_euro, Sum(qrygphursubform.total_ euro) AS total_euro
FROM qrygphursubform
WHERE (((qrygphursubform.newreco rd)="Yes") AND ((qrygphursubform.imsi)=[F orms]![frm gphur]![im si]))
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?
SELECT qrygphursubform.company, Sum(qrygphursubform.voice_
FROM qrygphursubform
WHERE (((qrygphursubform.newreco
GROUP BY qrygphursubform.company
HAVING (((qrygphursubform.company
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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_eu ro) AS sms_euro, Sum(qrygphursubform.gprs_e uro) AS gprs_euro, Sum(qrygphursubform.total_ euro) AS total_euro
FROM qrygphursubform
WHERE (((qrygphursubform.newreco rd)="Yes") AND ((qrygphursubform.imsi)=[F orms]![frm gphursubfo rm]![imsi] ))
GROUP BY qrygphursubform.company
HAVING (((qrygphursubform.company ) Not Like (([qrygphursubform].[compa ny])="OM" Or ([qrygphursubform].[compan y])="BS" Or ([qrygphursubform].[compan y])="D2" Or ([qrygphursubform].[compan y])="F2" Or ([qrygphursubform].[compan y])="VF" Or ([qrygphursubform].[compan y])="LT")) );
any suggestions on this one.
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_
FROM qrygphursubform
WHERE (((qrygphursubform.newreco
GROUP BY qrygphursubform.company
HAVING (((qrygphursubform.company
any suggestions on this one.
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°)
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°)
ASKER
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.
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" ,"qrygphur sumonnetwo rk")
That should work. Then try:
? DSum("voice_euro+sms_euro+ gprs_euro" ,"qrygphur sumoffnetw ork")
Do you get a better error message?
(°v°)
You get a more explicit error message from the immediate pane (Ctrl+G). Try:
? DSum("voice_euro+sms_euro+
That should work. Then try:
? DSum("voice_euro+sms_euro+
Do you get a better error message?
(°v°)
ASKER
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
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
ASKER
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.
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°)
Success with your project!
(°v°)
=DSum("[voice_euro]", "qrygphursumonnetwork") + DSum("[sms_euro]", "qrygphursumonnetwork") + DSum("[gprs_euro]", "qrygphursumonnetwork")