Turn a query into a function in access 2010.

I have been having a lot of problems with query that is in a textbox. I would like to get help and turn it into a function then i wouldnt have problems doing other calcualtions in my textboxes. I have the Idea but Im not sure how the code would be.
If anybody knows a better way then using a function please let me know.

I am trying to calculate check boxes.  each check box is  worth a percentage.
so you have

 for example a installation of a car

Name    Door Installed   window installed Hood installed total percenatage
Prius       10%                       50%              40%                      100%
Linc         20%                       30%              50%                      100%
Prius       10 %                      70%                0%                         80%
  Total                                                                                        280%

Here is the code:Percentage it is in a query and when you check a field it calculates the percentage of the amount done for each item.:

Percentae: 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%")

So I thought I could do a function\

Some function_()

Check Mark 1 = 10%

Check mark 2 = 30%

Check Mark 3=  20%  etc.

then it totals and divides each check mark by 100 then I can do my calculatios

Total percentage of all
total percentage of each
total percentage of each make of car

 Here is the database it is not about cars though I just made this for example.
Who is Participating?
als315Connect With a Mentor Commented:
Look at sample. May be it is not concerned with your question, but it could be a step to.
als315Connect With a Mentor Commented:
Have you tested sample from my comment #a38749994 ?
gigifarrowAuthor Commented:
alsThanks for the help but that was the wrong field that I am trying to calulate. I have a query in the recordsource of the field the name of the field is called percentages.

It gives the percentage complete for each record. I looked to see the name and this is what I got below. Im not sure if that is the right name but that is what is on the left tab of the query.  I need more calculations from this field. Dsum doesnt seem to work for queries in a text box but dLookup does but doesnt total. so that is where Im stuck at. In that table I need the total of each and the total of all.

=DSum("[Percentages]","frmTempleFullUp:Query Builder") If you get time please look at the field again.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

als315Connect With a Mentor Commented:
I don't undrstand idea. May be sample is too far from reality.
You have table with columns: Production (ID ?, but almost always empty), Unit where are some duplicates. On your form nothing is filtered.
May be you can prepare sample in Excel of expected result? I also don't understand percent > 100
In your example:
Name    Door Installed   window installed Hood installed total percenatage
Prius       10%                       50%              40%                      100%
Linc         20%                       30%              50%                      100%
Prius       10 %                      70%                0%                         80%
If you like to calculate some totals, may be you need sum of percents divide to count of lines? In this example Total may be 280/3=93.3
I can write function, where we can calculate totals from your recordset, but I should understand idea.
Try this query (qrytemple):
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, -([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 AS Percentage
FROM tblTempleFacilityFullup;

Open in new window

Do not convert percents to integers, you can always use percentage format on your form or report.
You mean like this?

gigifarrowAuthor Commented:
Yes,  I hartono thats part of the idea. How did you get the total on the right bottom?
I also need a seperate total of percentages for the Primus so that total would be 150%.

Each check is worth a percentage when they check it calculatets the total amount of percentage of checks until it gets to 100.

On my sample database all the calculations are done in text boxes. I have trouble working with queries. Im still not good in ACCess.

I guess my dummy data should have all the fields filled in Let me .

fill the fields out I will give you the excel spreadsheet that all the callculations need to be done. Which I have to take that sheet and make it look exactly like Excel done in  Access.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.