Link to home
Start Free TrialLog in
Avatar of gigifarrow
gigifarrow

asked on

Use query data to do a total calculations in a text box on a form.

I have a field in a query that is called Percentages.


i Have a text box that needs to take the information from precentages and total the precentages. I can total a table information is it possible to total a query caluculation in a text box?


=DSum("[Scope]","tblScopeUnit")


Like this some how?

=DSum("[Percentages]","Queryname")

Here is my database



for example
 A door installed  a=10% b=20% c=30%    percentages = 60%
 B door installed  a=15% b=30% c=20%    Percentages =  65%

Total= 125%
MODKitProduction.zip
Avatar of etech0
etech0
Flag of United States of America image

Yes. Set the datasource for that textbox to be =DSum("[Percentages]","Queryname").
Avatar of gigifarrow
gigifarrow

ASKER

thank you for your time and help! But the query

is embedded in the form in the record source . so I dont know what the name would be
I used

=DSum("[Percentages]","frmTempleFullUp:Query Builder")

i get  this   error? as an error.



if you have the time could you please look at my database and tell me what the name would  be,

in the record source of the form it has this

SELECT tblTempleFacilityFullup.Production, tblTempleFacilityFullup.InShopDate, tblTempleFacilityFullup.SerialNumber, tblTempleFacilityFullup.Unit, tblTempleFacilityFullup.Induction, tblTempleFacilityFullup.TearDown, tblTempleFacilityFullup.Provision, tblTempleFacilityFullup.E1GroundInsert, tblTempleFacilityFullup.ReliefHoleExtinguisher, tblTempleFacilityFullup.AssemblyA, tblTempleFacilityFullup.FuelShotOffSleeve, tblTempleFacilityFullup.AsemblyB, tblTempleFacilityFullup.CEPUpgrade, tblTempleFacilityFullup.AFESCEPSwitch, tblTempleFacilityFullup.AssemblyC, tblTempleFacilityFullup.ERRHandle, tblTempleFacilityFullup.DriverSeat, tblTempleFacilityFullup.AssemblyD, tblTempleFacilityFullup.HotBoxEnhanceMent, tblTempleFacilityFullup.AssemblyE, tblTempleFacilityFullup.LegacyBattery, tblTempleFacilityFullup.[LED/ERR], tblTempleFacilityFullup.T161Track, tblTempleFacilityFullup.AFES, tblTempleFacilityFullup.HotBox, tblTempleFacilityFullup.BASSM2, tblTempleFacilityFullup.GunnersSeatStopRework, tblTempleFacilityFullup.RoadTest, tblTempleFacilityFullup.QC, tblTempleFacilityFullup.OutDuction, tblTempleFacilityFullup.PercentageComplete, tblTempleFacilityFullup.DateCompleted, tblTempleFacilityFullup.DateReturned, tblTempleFacilityFullup.Remarks, Format(Abs(([tblTempleFacilityFullup]![Induction]*6+[tblTempleFacilityFullup]![TearDown]*10+[tblTempleFacilityFullup]![Provision]*10+[tblTempleFacilityFullup]![AssemblyA]*10+[tblTempleFacilityFullup]![AsemblyB]*10+[tblTempleFacilityFullup]![AssemblyC]*10+[tblTempleFacilityFullup]![AssemblyD]*10+[tblTempleFacilityFullup]![AssemblyE]*10+[tblTempleFacilityFullup]![RoadTest]*4+[tblTempleFacilityFullup]![QC]*10+[tblTempleFacilityFullup]![OutDuction]*10)/100),"00.0%") AS Percentages FROM tblTempleFacilityFullup;
This way might be easier:

In the properties box for the form, click the button with three dots next to recordsource.

Then, add a field to the query, and you can have it do the calculations. Once youpen the querybuilder to do that, you should be able to see the names of your fields.
Avatar of als315
You can sum field directly (Procent fields were changed from text to numeric). Look at sample.
But may be better to use subform with results from query. Dlookups took too many time.
MODKitProduction.zip
"frmTempleFullUp:Query Builder"
..is that the actual name of the table/query?

Try changing it to something without the special characters...
When I click on the record source to see the the query on the tab it has the name "frmTempleFullUp:Query Builder" .

I dont know how to change it because it doesnt show up as a query,on the Navigation under queries.

 I need the total of precentages from a field that is not in the table but was addd to  the query.
So when I put,
TotalPrentages:=DSum("[Percentages]","frmTempleFullUp:Query Builder")  
 error says cant find table or query.


When I go to save it to see what the name of the query is. It ask me if I want to save the record of the SQL statement? it doesnt tell me the name of the query.
Go to the record source box, and click the little button with three dots that you'll see at the right side.
When I click on the record source where the dots are on the right side the query opens up. on the tab it has the name "frmTempleFullUp:Query Builder" .

When I go to save it to see what the name of the query is. It ask me if I want to save the record of the SQL statement? it doesnt tell me the name of the query.

When I put it in a text box I get a error. It seems to me that you cant use DSum with a query but you can use a Dlookup but it doesnt total with a Dlookup. made a new query called qryTemple and tried this.

=DLookUp("[Percentage]","qrytemple"," [Unit ]= ('278th ABCT')") works but doesnt total
=DSum("[Percentage]","qrytemple"," [Unit ]= ('DRS TEST')") gives me a error everytime?
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try taking out the parentheses and brackets from this:
=DSum("[Percentage]","qrytemple"," [Unit ]= ('DRS TEST')")

Like this:
=DSum("[Percentage]","qrytemple"," Unit = 'DRS TEST'")

If that  doesn't work, also try taking out the single quotes from around DRS TEST.