Calculations In Forms...
Apr 7, 2006
Hi Everyone:
I have a question that I can't seem to figure out. I am trying to create a form in which there are three major fields. 1) Value 1 2) Value 3) Sum of Value 1 & 2. I want it so that two fields are added up and then the sum, which will populate in the third field, will be stored in a table.
Does anyone have any suggestions on what I can do? I am fairly new to access and haven't had much luck finding anything on the net.
I appreciate any and all advice.
Thanks!
Darin
View Replies
ADVERTISEMENT
Sep 28, 2015
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
View 14 Replies
View Related
Oct 11, 2004
Hello
I have this problem. I've made a table called "producers" and a main form based on this table. I've also made a table called "orders", with several data regarding orders, and a secondary table based on the "orders" table. These tables are linked. So these forms show the orders made by each producers. Now , I try to add the "price" field of each record of the secondary form and then show the result in a text box in the main form, so that I can have a total price amound for each producer.I've tried many things but nothing seems to work. Is this so difficult to achieve? Pls help. Thank you in advance!
View 14 Replies
View Related
Jun 27, 2013
performing calculations in a form in an Access 2007 database.The form is used to generate invoices and sales receipts. At the bottom of the form there are 3 text boxes: "Sub-total" (Text128), "VAT" (Text130) and "Total" (Text132). Each text box is bound to the Control Sources "Sub-Total", "VAT" and "Total" respectively in a table called "Orders".
I would like the "VAT" text box to automatically calculate the VAT on an order and enter it into the VAT field in the table "Orders". To do this I have tried using the formula:
Code:
=[Text128]*0.2
I would also like the "Total" text box to automatically calculate the total cost of the order and enter it into the Total field in the table "Orders". To do this I have tried using the formula:
Code:
=[Text128]+[Text130]
I know that these calculations are correct as they work in an unbound text box however I cannot get this to work with bound text boxes.Is it actually possible to do this or do I need to use another method to perform these calculations?
View 13 Replies
View Related
Jan 21, 2005
Hi Everyone! Bear with me, I'm new to Access and I may not understand or use all the terminology correctly.
Background: I'm creating a database to track the inspections of many businesses. Each business will have many inspections throughout the year and each inspection has many items. I have a form set up which includes Business info and Inspection info (#items inspected, subtotal for inspection) on the main form, and Items in error listed on a subform (no error=no items listed).
Problem 1: I need to make a count of all items that are listed. I can do this in the subform, with no problem (in one of the footers #Discrep:count([item])) but now I need to report and use that value on the main form to calculate the percent error for the entire inspection. I don't know how to pull in onto the main form to set up my calculation. :confused:
Problem 2: This one is related and is probably the same fix. I have PriceCharged and PostedPrice for each item. I need to calculate the net error and then, using the subtotal on the main form, calculate the % dollar error.
Any suggestions, oh great and powerful gurus of the Access world? I hope I explained that clearly enough for you.
Thank you very much in advance!
~Lith
View 3 Replies
View Related
Mar 18, 2013
I created a Form to enter data into a table. Two fields are numeric and typed into, a third field calculates the ratio instantly. I did that by entering "=field1/field2" into the control box in the properties panel for that form element.
However, when I look at the table, all the data goes in except this calculated ratio. Is there a way to get the form to "write" it to the table?
Alternatively, how to get the table to act like a spreadsheet and automagically calculate that third column, then it actually doesn't even need to appear on the form.
View 5 Replies
View Related
Jun 11, 2013
I am currently working on a form that calculates the sum of counts and charges for various categories. The form sums the number of items in each category, then the sum of the charges. Where I am stuck is trying to break out the "per unit" charge for each category. The calculation works fine (sum of charges/sum of items) when there is a number greater than 0, but if the count or charges are 0, the fields display errors. I attempted to compensate for this by setting the text box value on the form to iif(iserror(sum of charges/sum of items),0,(sum of charges/sum of items), but it still shows the error!. I could probably write nested iif statements to evaluate the values of each sum, but you would think this would be simpler. Here is a sample of the actual ControlSource field on one of the text boxes:
=IIf(IsError([SumOfOracle_Qual_Dev_Charge]/[SumOfBaseline Qualified Developer]),0,[SumOfOracle_Qual_Dev_Charge]/[SumOfBaseline Qualified Developer])
View 2 Replies
View Related
Feb 22, 2005
Hi all,
I have a data entry form with several values that could be calculated and entered if I knew how. The FAA requires logging and tracking values that result in the following fields:
TimeSEL
TimeXC
TimeNight
TimeDay
TimeDual
TimePIC
TimeTotalFlight
My last flight resulted in entering 3.4, 3.4, 0, 3.4, 0, 3.4, 3.4
My question is: is it possible to add some sort of test expression in the TimeXC field which would examine an associated check box (i.e. "Was flight XC?") and if it was checked then would automatically calculate the flight hours (TachStop-TachStart) and enter the value in the TimeXC field?
Thanks for any help.
View 2 Replies
View Related
Apr 3, 2013
I have a dashboard that opens with my main form and it contains many textboxes with Dcounts, Dlookups and Dsums in it. I am hoping that I can put these textboxes on a separate form that is hidden unless a command button is clicked to make it visible.
I know how to do this, but my question is whether or not all of those queries will run whether or not the form is visible. Obviously if the Dsums etc.. are going to run anyway, then I won't bother.
Is there a better way to do this?
View 1 Replies
View Related
Nov 7, 2014
I have a form bound to a table, with a subform in there, and I have a textbox with a source one of the table's fields. I would like the value of this textbox to be calculated based on the values of some boxes in the subform, but I also wont to be able to edit the value on the textbox myself.
Is it possible to have a textbox that get its value both from calucaltions and user input?
View 1 Replies
View Related
Mar 14, 2013
I have a subform based on a query. That query contains a couple of calculations that end up in textboxes on the subform. The problem is that the calculations require me to enter 2 dates that are on the subform. If these are not entered before the calculation is performed I get a "Cannot update record" error message and then #error in both Lease cost and Discount Cost textboxes.
View 6 Replies
View Related
Dec 18, 2014
All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other. I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following..."if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox..Likewise with the credit box, and all works perfectly fine.
However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway.
View 13 Replies
View Related
Nov 20, 2014
I am trying to get the main form to display a total Amount for all records in Purchase order details but the control displays Error:
I have a Form called frmPurchaseOrder with a sub-form sbfrmPurchaseOrderDetails
sbfrmPurchaseOrderDetails takes item details from a table based on what is selected in the combo box then fills out four additional fields in the same sub-form.
SELECT tblItemListDetails.ItemID, tblItemListDetails.ItemName, tblItemListDetails.ItemSize, tblItemListDetails.ItemUnit, tblItemListDetails.ItemUnitCost, tblItemListDetails.ItemBrand FROM tblItemListDetails WHERE (((tblItemListDetails.Supplier)=[Forms]![frmPurchaseOrders]![SupplierCombo]));
Private Sub cmbItemName_Change()
Me.txtSize.Value = Me.cmbItemName.Column(2)
[Code] ....
View 5 Replies
View Related
Jun 8, 2005
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
View 1 Replies
View Related
May 3, 2005
Hi ive made a query but instead of calculating the total at the end of each line I want it to add up the total quantity at the end.
For example:
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
How do i get the query to add up all of those quantities?
View 5 Replies
View Related
Oct 30, 2005
I'm hoping this is a no brainer...I am a complete newbie and self taught so apologies for not knowing the correct techie terms!!
I have a db with products...
Each product has a price (to customer) and cost (to me the seller)
What I want to work out is the margin i.e. price minus cost divided by proce multiplied by 100 to give a margin in percentage terms.
The data is held in a table with a record per product with the price and cost..
I want to build in another fieled per record that calculates the margin...
Thanks very much
Jessica
(I would also like to be able to work out decreasing margins if I was to extend say a 10% or 20% discount to the customer but I guess I shoudl walk before I run!!)
View 3 Replies
View Related
Nov 15, 2005
Hi, I have a database and on one table I have dates stored for certain jobs. I was wondering if anyone knew how to automatically calculate an end date if the start date and duration is entered!
Do I use a certain query?
Hope you can help, Tobi :)
View 2 Replies
View Related
Jul 26, 2006
Hello all,
I'm after some advice. Access is not one of my strong points (even though I use it alot in work!)
Anyway, is it possible to create calculations in access similar to those in excel????
View 2 Replies
View Related
Mar 18, 2008
Ok I'm really new at using access so go easy on me. I am trying to write a database to keep 30 measurements of a part grouped by lot number and then calculate the standard deviation, mean and a few other calculations, however I am unsure about how to do the calculations in access.
I would like the database operator to be able to enter the data then print out a report per lot, with the entries and the calculated answers.
I'm just unsure where and how to do the calculations... any help or suggestions?
Thanks in advance!
View 14 Replies
View Related
Mar 30, 2007
Hi,
I've read plenty of advice warning against performing calculations in tables. What is the best way for me to achieve this.
I need to calculate the product of a number of fields.
Field1 * Field2 * Field3 * Field 4 = Value
Field1 and Field 2 change periodically
Field3 and Field4 change less frequently
I want to be able to perform filter queries using the resulting value
i.e return all items with a value greater than 100
Thanks in advance.
Al
View 2 Replies
View Related
Apr 13, 2008
I'm having a problem with this age formula in the query :
AGE: DateDiff("yyyy",[birth_date],Date()) & " " & " years" & " " & "and" & " " & DateDiff("m",[birth_date],Date()) Mod 12 & " " & "months"
Need your help guys in 2 probs :
1. age calculated is not right.
2. need to compute what w'd be age on the sep 1st 2008 - how do I do it?
Appreciate your help.
Thanks.
View 2 Replies
View Related
Apr 26, 2005
Hi,
I'm trying to a create a report to list results for a running club based on series-to-date. To do this, I allocate points to the first 100 runners to cross the line in each race. The winner gets 100, second place 99, and so on with the 100th person getting 1.
In the database i have 1 table holding member details, then one table holding races. I also have a table linking the 2 called race_entry so i know what members have entered for which races. I have one other table called race_result that links the runner to a race and holds the position they finished in the race.
How could i use the data in the race results table to add up all the points they earned by looking at their finishing position in the races they run and then listing the points in order in a report.
I assumed it wouldnt be necessary to have a point field in the table as its deriveable.
Any help urgently needed!!!
View 1 Replies
View Related
Jul 13, 2006
This is more than likely simple for a lot of people out there but it has me stumped. I have a form that has a two combo boxes with dates in both ( so the user to pick from and to dates) now I am always going to be adding more dates as time progresses. I need the next form to add the info with the dates no mater what the user picks.
For example I am going to (once a week) be adding new info to the table.The end user is going to pick 06/24 (to indicate the end of the week) in both forms or 06/24 in the from box and 07/01 in the to box. In all situations I need the next form to add the data together.
Sorry for the long post but didn't know how to state it any simpler.
View 10 Replies
View Related
Dec 6, 2007
Hi everyone!
I am a beginner with Access and was wondering if someone would be so kind to give me some pointers
I have created a form where the user can select which dates they require a hotel for the evening. I was looking for some advice on how I can go about putting an area on the same form which calculates a sub price for the days they have selected. If I am not being clear then ask for more details.
Thanks
View 2 Replies
View Related
Aug 24, 2005
I need to be able to use some functions available in excel in an access 2000 database. These include SUM and IF functions. If any one can help me figure out how to do this any help help wouold be greatly appreciated
View 6 Replies
View Related
Mar 7, 2006
Hi
I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.
Dim valuation1 As Date
Dim valuation2 As Date
Dim valuation3 As Date
Dim valuation4 As Date
Dim valuation5 As Date
Dim valuation6 As Date
Dim valuation7 As Date
Dim valuation8 As Date
Dim valuation9 As Date
Dim valuation10 As Date
Dim valuation11 As Date
Dim valuation12 As Date
Dim startdate As Double
Dim findate As Double
Dim valcounter As Double
'retrieve start date and end date
startdate = CDbl(Me.Start_Date)
findate = CDbl(Me.Completion_Date)
'calculate evaluation periods
Dim valCount As Double
Dim valDays As Double
valCount = CDbl(Me.Valuation)
valDays = (startdate + ((findate - startdate) / valCount))
'Work on entering valuation dates
Do While valcounter < valCount
Select Case valcounter
Case "1"
Me.valuation1 = startdate
Case "2"
Me.valuation2 = startdate
Case "3"
Me.valuation3 = startdate
Case "4"
Me.valuation4 = startdate
Case "5"
Me.valuation5 = startdate
Case "6"
Me.valuation6 = startdate
Case "7"
Me.valuation7 = startdate
Case "8"
Me.valuation8 = startdate
Case "9"
Me.valuation9 = startdate
Case "10"
Me.valuation10 = startdate
Case "11"
Me.valuation11 = startdate
Case "12"
Me.valuation12 = startdate
End Select
valcounter = valcounter + 1
startdate = startdate + valDays
Loop
' this value is to test the interval period
Me.tdays = valDays
I am new to vb and so ths code is not going to be the best or anywhere near but any help would be great.
Thanks
View 1 Replies
View Related
