Calculate On Form
Mar 9, 2005
Hi,
I have a from with 3 fields on it where users can enter numbers of boxes attached to a booking.
Each field stands for a box of a certain type.
E.g.:
20's ==> 1
40's ==> 1
45's ==> 1
Now, I want to calculate a total based on 40 being 1, 20 being 0,5 and 45 being 1,125.
The total in this case would be:
2*20 = 0.5
1*40 = 1
1*45 = 1.125
Total: 2.625
Is this possible, and, if yes, how do I insert this calculation?
Don't know if this is clear enough, if someone has any questions on my goal here, please ask me to clarify...
Thx once more,
Lion85heart
View Replies
ADVERTISEMENT
Mar 27, 2006
I have a form (frm_Active) that reads from a query (qry_Active) based on a table (tbl_Data). The form/query/table has the following fields:
EVENT DATE
START_DATE
REVIEW_DATE
APPROVED_DATE
POST-REVIEW_DATE
When I add a new record, I would like for the default dates to be populated based on the EVENT_DATE. For example, if I enter 1/1/2006 for the EVENT_DATE, I want the
START_DATE = +7 days
REVIEW_DATE = +55 days
APPROVED_DATE = +60 days
POST-REVIEW_DATE +180 days
I tried to do this in the table as the "default value" and it would not accept it. I don't think I can do this in the query because the query is basically displaying the table and I want to be able to manually enter a different date if needed so it will be stored in the table. I tried this on the form in the "Default Value" field, but it is just blank when I enter a new record and enter the EVENT_DATE. However I am not sure I am entering the formula correct. Can someone tell me what is the best way of doing this and what the correct formula is?
Thanks,
Jim
View 9 Replies
View Related
May 4, 2006
Hi, Thanks in advance. I am trying to calculate percentage, it has to round to the nearest, and also if the percentage is greater than 100 then it has to write 100%.
formulae: c=(a/b)*100,
example: c=(8/3)*100 =266.66% but this is more than 100% so i need this as 100%
example2: c=(2/3)8100=66.66% but i need it to round as 67%
i've 3 controls on a form a,b,c. so c has to calculate by itself when they enter data on a and b.
thanks,
sam
View 1 Replies
View Related
Feb 5, 2014
I am having trouble on the Work Orders Form to get the SubTotal to calculate correctly.
The SubTotal Control Source is:
Code:
=DLookUp("[Services Total]","[Services Total]","[WOrderID] = '" & [txtWOrderID] & "'")+DLookUp("[Parts Total]","[Parts Total]","[WOrderID] = '" & [txtWOrderID] & "'")
The Form Record Source is:
Code:
SELECT DISTINCT [Work Order].*, [Payment Total].[Payment Total], [Services Total].[Services Total], [Parts Total].[Parts Total] FROM (([Work Order] LEFT JOIN [Parts Total] ON [Work Order].WOrderID = [Parts Total].WOrderID) LEFT JOIN [Payment Total] ON [Work Order].WOrderID = [Payment Total].WOrderID) LEFT JOIN [Services Total] ON [Work Order].WOrderID = [Services Total].WOrderID;
why my form won't calculate totals?
View 2 Replies
View Related
Sep 28, 2013
However Instead of entering the postal code continually or selecting from the combo box. How do I set zipcode 1 and zipcode 2 based on fields in a form. I would like to use the current FROM (Default zip based on the current job in form) and TO zipcode [Project Postal Code] that is on the form at that time. Not necessarily combo box.
Currently you have
zip1 = Me.Combo2.Value
zip2 = Me.Combo4.Value
I want Zip 1 to be Default zip (however I can still change it if needed) ZipCode field is [Project Postal Code]
Here is the full code:
Private Sub Command6_Click()
Dim zip1 As String, zip2 As String
Dim sResponse As String
Dim sLink As String
On Error GoTo Command6_Click_Error
zip1 = Me.Combo2.Value
[code]....
View 1 Replies
View Related
Nov 18, 2014
I'm trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can't use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.
Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.
I created a query (qryFindJob#s) that list all of the Job Numbers, but I'm not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.
View 7 Replies
View Related
Apr 16, 2012
I have a form, with a sub-form, from which a call a datamacro to calculate totals(from the same table as the control source of the sub-form) and then want to display the totals on the parent form!! The RETURNVARS all have the correct total values, but i cant assign them to a control on the parent form!! none of the controls are recognized when i try and set the PROPERTY VALUE to the totals? (spellings are definitely correct)...
View 2 Replies
View Related
Aug 24, 2013
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
Code:
Me.txtTotalEarnings = DSum("Price", "KatiesPeriodTakings", "AppDate" >= CDate([Forms]! _&
[frmKatiesTakings]![txtStartDate]) And "AppDate" _&
<= CDate([Forms]![frmKatiesTakings]![txtEndDate]))
But I keep getting a mismatch error
The Query is called KatiesPeriodTakings
The field I want totalled is called Price
The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
View 5 Replies
View Related
Jul 8, 2015
I need to calculate the passed working days and show them in a form. I should be also able to use the number in a query later on. in excel I use the formula to get the days passed:
Code:
=IF(ISERROR(MATCH(F10,Dictionary!C:C,0)),NETWORKDAYS(D10, TODAY(), Dictionary!$E$2:$E$43),"Status Excluded")
D = "Date_uploaded" in access table "tbl_All_Cases"
F = "Status_Case" in access table "tbl_All_Cases"
c:c = dictionary case status
E:E = dictionary holidays
"Status Excluded" will show up in the cell if a case has one of the status from the dictionary..I created a table: tbl_Dictionary where there are 2 fields: "Case_Status" and "Holidays".How can I translate the above formula into something that access will understand?
View 14 Replies
View Related
May 20, 2014
I need to make an order purchase form like i have customer table and product table, i want to make a form which creates order for a specific customer and I can add as much items as i want and than calculate auto the total price, and an option to make a report for it.
View 5 Replies
View Related
Dec 16, 2004
How would i go about getting the field 'GMargin' (Gross Margin) to be calculated from subtracting 'SellingP' from 'PurchaseP' (Selling and Purchase Prices)?
How do i do this? Help me please :)
View 5 Replies
View Related
Oct 6, 2005
I have a field which I enter a short time such as 3:13, 5:45, etc
Now i am creating a report which at the end of the report I would like to include an unbound text box, which displays the total amount of time for that field (pls note that the total will never exceeds 24 hours) What is the best formula to use in order to calculate this ?
Cheers
View 4 Replies
View Related
Jan 18, 2006
Hello,
I am trying to sum minutes from a database using an Access query, but the format in the database is hh:mm. I know the query to use in Oracle:
ROUND(SUM((substr(actual_time,1,(instr(actual_time ,':' )-1))) *60 + (substr(actual_time,(instr(actual_time,':' )+1),2)))
but not sure of the syntax to use in an Access expression.
Any ideas?:confused:
View 1 Replies
View Related
Aug 6, 2006
HI,
Excel is having difficulties calculating a workbook of mine. It complains about cirkular references, so I had to set the iterations manually.
The workbook holds about 5000 records and I´m using a cosinus function.
The thing is I don´t know what to do. Excel calculates the workbook, but it doesn´t do it right. What setting should I use with the iterations?
I´m thinking of running it in excel 97. Will that help?
I really need to be able to run this workbook. Does anyone have an idea?
Fuga.
View 6 Replies
View Related
Jul 10, 2007
Hello, I didn’t know how to explain this or where to post this but here we go! I have two databases and one of the PC's here at work can’t use some of the items in the database like on one of the databases there is some formulas in text boxes and that PC won’t calculate what’s needed in them and every PC in the plant will except for this one PC. Also the one PC won’t bring up the Allen Browne's Calendar. When you press the start or end button to bring up the calendar it never pops up but if I enter the start date and end dates and use it that way ect then it works. I have no clue why this is just happening to this one PC. Any ideas?
View 2 Replies
View Related
Jan 19, 2006
I want to calculate a data from anthony table, like (20 Jan 2005) + 365 days
will be 20 Jan 2006, how can i do it.
The Table setting is just like below:
Constituent (DataID,...);
Student (DataID, DOB,...);
School (DataID, 4YearsOld(As a Date Format),...)
Anyone can help me, Thank you.:)
View 3 Replies
View Related
May 12, 2007
i have a table with fields "[DATE OF BIRTH]", "age", "temp-age"
i have a query that calculate the age:
example; the query will get user to input the "temp-age" and upon "temp-age" is input and "ok" on the query, it will calculate a person's age and store the value inside table "age".
however, the calculated value for the age have some data problem. for example, when a person whose age is yy:mm 24:05 the age inside the table will be 1924:05. there will be a additional "19" at the age.
what cause the additional "19" problem?
---------------------------------------------------------------------
my query in design view is like this; (see attachment)
it uses " [temp-AGE]-[DATE OF BIRTH] " to calculate the age and store into table "age".
View 1 Replies
View Related
Nov 10, 2006
Hello,
I'm trying to calculate months not days between
EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?
:confused:
View 2 Replies
View Related
Nov 19, 2006
Hi, i'm trying to calculate the amount of employee for each company giving me a total in the queries.. how do i go about this? I'm not an person who uses Access to solve this. I have amount 20 Companies stored and over 800's employee.. Here are the field in the table:EmployeeIDEmployeeNameNumberofEmployeeThanks! Hope this helps.
View 1 Replies
View Related
Jan 15, 2007
Hi
I am very new to databases, and think i am a bit over my head. I am doing a database for vacation rentals, and am trying to calculate commissions, basically i wanted to have a set of checkboxes, labeled 10%, 15%, 2.5% and $25, with any combination of these checkboxes being able to be checked.
i want the query to work like this:
If the 10% checkbox is checked i want the query to take the SubTotal for the reservation, and calculate 10% of that number. if the 2.5% checkbox is also checked, i want the query to calculate 2,5% of the reservation and add it to the commissions total (10% + 15%)
I think(hope) that i explained that clearly? Can this be done?
Thanks for your help!
Ben
View 2 Replies
View Related
Mar 16, 2007
What is the easiest way to calcuate a median value for a group of values?
View 4 Replies
View Related
Apr 19, 2007
I am attempting to calculate the Average & Median on a particular field in a query.
=Median("qry2007_KPI_AvgJan_A","ContractSignedToReceived")
query name is qry2007_KPI_AvgJan_A
field name is ContractSignedToReceived
I am getting #Name? error.
Any ideas why? Thanks for any help troubleshooting this.
View 1 Replies
View Related
Mar 22, 2008
In a report, I need to calculate the percentage of clients that respond "Yes" to a question on a survey. I need to create a query that will return the count of the number of "Yes" responses, and the number of total responses.
For example, if 10 clients complete the survey, and seven respond "Yes", I need the 2 fields in the query to be 7 and 10. So far, I have only been able to do this using multiple queries.
Thank you.
View 1 Replies
View Related
Aug 25, 2005
stuck in the middle of a project of employee payroll the following problems are for some suggestions:
if an employee is not taken off in a week and work on sundays, he's paid double OT; if he is not taken off he's not paid; i.e. he can take 4 days off in a month
how can i keep track of this in the db; can i use a table putting 7 days as fields and make it all yes/no so that user can check or uncheck the status against each employee(!); but for whole month how can i calculate
work period start from every month on 25th and ends on 24th in next month.
got tables for attendance and leave
help appreciated
View 1 Replies
View Related
Aug 31, 2005
I cannot figure this out,
I have values that need to be calculated in a field but it will not work with null values, I’m sure this is easy but I have not found a solution.
Thanks in advance.
The attached mdb illustrates my dilemma.
View 1 Replies
View Related
Sep 5, 2006
Hi, can someone help me with this formula.
i have one field called notice_1 this field is a combo boxs multiple choice ( YES - NO )
Then i have 1 field called score_1 ( if notice_1 = "YES" then let score_1 = 5 else then let score_1 = 10 )
Does this make sense , I am an, amateur at access 2003,
Thank You For any help i can get...
John Calcitrai
View 5 Replies
View Related