Error With Form Calculation
Apr 21, 2005
Hi!
I've encountered another stumbling block. My form is almost done except for some minor details. However, one calculation does not work even when I use the correct terms.
This calculates the tuition balance per customer based on data from 2 subforms on the main form: sbfTuitionAccount and sbfTuitionPayment.
My formula is =Nz(sbfTuitionAccount.Form![txttotaltuitionpayable])-Nz(sbfTuitionPayment.Form![txttotaltuitionpaymenttotal])
I've counterchecked all form and control names, however, when I test it, I only get a '#Error ' in form view. A similar formula has proven functional in another form. Do you have any ideas as to what the problem is?
Thanks!
View Replies
ADVERTISEMENT
Sep 6, 2005
I have a simple tabular form with the following:
cboItem_Name
txtItem_Cost
txtQuantity
txtExtended_Price
The AfterUpdate for txtQuantity = Me!txtExtended_Price = Me!txtItem_Cost*Me!txtQuantity, which puts the extended price in the control.
In the FOOTER of the form, I have txtSub_Total. It's Control Source is:
=SUM([txtExtended_Price]).
As far as I can tell from reading, when placed in the form footer and used in 'continuous form' layout, this ought to give me the total cost of ordered items.
Instead, I am getting #error. Can anyone help me with this?
View 13 Replies
View Related
Aug 16, 2005
Hi everyone,
What I wan't to do seems very hard, and am wondering if anyone would be able to help out.
Here is what I am trying to do:
First of all, so you somewhat understand my field headers and table, I work for an oil company. I have a form that is based off a table and some of the fields include "Date", "Well Name", "Stock", "Sales", and "Oil". In my table, data is entered for each well every day.
Here is an example of my table:
Date Well Name Stock Sales Oil
08/15/2005 Well1 140 44 25
08/16/2005 Well1 120 35 15
08/15/2005 Well2 150 49 22
08/16/2005 Well2 115 40 30
What I want to do is create a text box on my form and I want it to do a calculation to see if the data is correctly entered. Here is the formula that I want to use in this text box:
If ([Stock] - [StockFromPreviousDay] + [Sales]) = ([Oil])
{
Print "OK"
}
Else
{
Print "Error"
}
I just can't figure out how to get the Stock from the previous day to work with. I have been stuck on this problem for about a week now, so any help would be gratefully accepted.
Thanks,
Chris
View 14 Replies
View Related
Mar 27, 2006
Having a problem with results on a form. Three unbound text boxes. One called "BillableDays" second called "DailyAmount" and third "PartialInvoice"
Now "PartialInvoice" is a result of [BillableDays]*[DailyAmount]
But........ The figures are not correct.
For example.... [BillableDays] = 7 ... [DailyAmount] = $135.42 and the result in [PartialInvoice] is $947.91 ...........
Any thoughts on why this is happening and how to correct??
View 6 Replies
View Related
Oct 6, 2004
I have a database which is used by operators to input manufacturing data i.e. LotNo, Customer, SalesOrderNo, PartNo, LineNo, ActualQty, Length, ScrapMat, (MatStdCost), StdLineSpeed, SetupTime, RunTime, LostTime and weightperpiece.
We then use this data to produce information like
Standard Run Time: (([ActualQty]*[Lenght])/[StdLineSpeed])/60
Machine Efficiency: ([Standard Run time]/[Runtime])
these work fine if values are inserted. The problem is that when a value is zero it returns a #error value in the field. This is probably caused by dividing by zero which can happen.
A test may occur where there is zero StdLineSpeed etc.
Is there any way I can bet access to return zero in these cases instead of the #error because I wish to do further calculations on totals and averages and I cannot if even one #error occurs.
Any suggestions or help would be appreciated.
Regards
Niall
View 7 Replies
View Related
Apr 24, 2005
I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:
ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"
I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:
LS5: ([q14]+[q15]+[q16])
It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.
Anyone got any ideas?
One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank
View 7 Replies
View Related
Sep 18, 2014
Trying to create a calculation in a field (text25) that excludes weekends and holidays. I am getting an argument not optional error.
Code : Me.[Text25] = GetBusinessDay([Due_Date - Result_Date])
View 14 Replies
View Related
Mar 7, 2007
Hi. I am new to this site, and fairly new to Access itself.
I am currently designing a database for work that allows people to store information about sites around Blackburn. There is one main form and 3 subforms inside of it.
On Subform Yield Assessment, I would like the database to calculate the DT_YIELD field by multiplying SITE_AREA_HA * DT_DENSITY.
The thing is, SITE_AREA_HA is not visible in Form View, and it is a dLookup field (gets its data from SITES_AREA_HA field in the Sites Details Subform). Also, the DT_DENSITY figure is determined from the combinations from the cascading combo boxes before it (DT_SOURCE_SUP, DT_LOC_TYPE, DT_SITE_AREA_HA and DT_TEMPLATE).
I have attempted to put the different formulas into the Caption Source as follows (without ""):
1. "=[SITE_AREA_HA]*[DT_DENSITY]" <-- didn't work
2. "=[FRM_YIELD].[SITE_AREA_HA]*[FRM_YIELD].[DT_DENSITY]" <-- didn't work, came with the #Name? error in the field
3. "=[SUBFRM_YIELD].[SITE_AREA_HA]*[SUBFRM_YIELD].[DT_DENSITY]" <-- same error as 2
4. "=[SUBFRM_PRIMARY].[SITE_AREA_HA]*[SUBFRM_YIELD].[DT_DENSITY]" <-- same error as 2
5. "=[FRM_PRIMARY].[SITE_AREA_HA]*[SUBFRM_YIELD].[DT_DENSITY]" <-- same error as 2.... etc etc
I have even tried making a command button, with the following code, but it still doesn't work - and this comes up with "Compile Error: Argument not optional":
"Private Sub CAL_YIELD_Click()
Dim fMultiply As Integer
If SITE_AREA_HA > 0 And DT_DENSITY > 0 Then
fMultiply = SITE_AREA_HA * DT_DENSITY
DT_YIELD = fMultiply
Else
End If
End Sub"
Please help, it is driving me nuts, I just can't seem to make it work, yet it seems so simple. Again, I am fairly new at this. The database is attached - form to open is FRM_PRIMARY.
Thanks.
View 3 Replies
View Related
Jan 25, 2006
I'm trying to figue out a way to make my database catagorize based on 2 variables, here's what i've got.
one variable is age, [age] and is entered directly into the form
the other is score, [pft total] and is a calculation performed with afterupdate functions.
what i would like to do is based on 4 age ranges and 3 score ranges (for each age) place the proper catagory (1st class, 2nd class, 3rd class) on the form.
for example a 27-36 yr old with a score of 200-300 is 1st class, but a 17-26 year old with a score of 225-300 is 1st class.
hope that's not to muddled
Jim
View 6 Replies
View Related
Sep 30, 2007
Is it posible to perform calculations within a table or data entry form. I have a date of birth field and an age field. I have an Update Action Query that automatically works out the date, (See below). However if I design the form with a close form event that triggers the query I get the confirmation message. I can only supress all Action queries confirmation messages or none at all and I do not want to disable this. Therefore I would like the age to be calculated within the table, (or form if that was possible). However I do not know how to do it.
DateDiff("yyyy",[DateofBirth],Now())+Int(Format(Now(),"mmdd")<Format([DateofBirth],"mmdd"))
View 2 Replies
View Related
Apr 3, 2008
In my Employees Sub form I have a calculation called Gross, the
calculation works fine but I can not get the query to gather the amount
View 1 Replies
View Related
Sep 23, 2005
On my form I have a number of boxes that contain the results of a query. The query itself contains calculations that total items from a number of other forms. Going back to the form, their is a final box that then holds another total. this time it is the result of all the boxes totaled topgether. My problem is that if one of the queryshas no data i.e there is no information on that form then no total result is produced. Ive tried putting a default setting of 0, but that dosen't seem to work. Basically I'm trying to say that if any of the criteria contains no values then it should be 0. Does this make sense?
View 9 Replies
View Related
Jul 22, 2004
i have a subform that has a field that has this (=Round([TotalHoursPoss]*[PercentField])*0.27) in the control source. the form is set to Continuous Forms. it then have a field in the form footer that i need a total of what ever numbers show up in the field from above. for example.
field=41
field=41
field=32
form footer:
total=114
but every time i try to put this "=sum([BudgetedNumberofSales])" in the field in the form footer it gives me an error.
any ideas of what i am doing wrong.
thanks
chad
View 7 Replies
View Related
Aug 22, 2005
OK, I figured out how to make a calculation. My big problem now is to have a global field, which would be "beginning balance" and "ending balance". I have 5 employees that share the budget. The budget starts at $1280.00. One employee may buy somthing one day and then another day while another employee may also buy something. I want to be able to put in the amount the employee spent and have it deduct from the balance. But then when I go to another employee and subtract what they spend I want that to subtract from the balance. I want the balance to be visible at all times no matter what employee I am on. Hope this makes sense. I have an attached file that shows how I did it for one employee. Cannot figure how to be able to do it for each employee and just the balance changes. Also I built a subform to be able to show different dates. But the problem again is having it subtract from one global balance. Thank you.
View 3 Replies
View Related
Jun 27, 2006
Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !
I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.
I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.
Suggestions please !
Steve
Private Sub cmdDate_Click()
Dim FirstDate As Date ' Declare variables.
Dim Number As Integer
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim lnginterimdate As Long
Dim intdays As Integer
Dim Msg
FirstDate = InputBox("Enter a date - dd/mm/yy")
Number = 20
dtinterimday = FirstDate
intdays = Number
Do Until intcount = Abs(intdays)
If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1
lnginterimdate = dtinterimday
If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then
intcount = intcount + 1
End If
End If
Loop
Msg = "New date: " & dtinterimday
MsgBox Msg
End Sub
View 5 Replies
View Related
Aug 18, 2006
Hi
I have 3 date fields. I am trying to work out number of days when
=[FIELD1 - FIELD2 OR FIELD3]
should do the calculation in field 4 NUMBER OF DAYS. but it comes up with date only. FIELD1 will always be filled, if field 2 is field then the number of days must be based on that. but if field2 is blank then it should be field1 - field 3. I may need a help on the formula does it need any if, else statement or not. If so can someone please help
View 1 Replies
View Related
May 30, 2013
I have a combo box on a form that is liked to a table (current). That table has a list of available items and a count at the end of it. For example, apples, bananas, strawberries, blueberries, 4. Is it possible to have the combo box on the form subtract 1 from the count when an item is selected from the list? So when apple is selected the count of 4 is changed to 3.
View 4 Replies
View Related
Nov 12, 2014
I have a field called PriceSoldFor and I need to calculate and display this field multiplied by 3.4%. This is what I have tried and reading elsewhere it should work.In the Control Source of an unbound TextBox I have entered the following:
=[PriceSoldFor]*[.034]
But when I view the form the field displays the following:
#Name?
View 2 Replies
View Related
Mar 6, 2008
I have a make table query which needs to take the column "Amount Orig Ccy" from the table "Consolidated Reporting Table Orig CCY", divide it by the Canadian Spot rate input by the user into text box "Spot" on the form "Summary Form", and then populate the column "Amount USD" with the returns.
Should I be using the DLookup function? I tried the below without success:
Amount USD: DLookUp("Amount Orig Ccy","Consolidated Reporting Table Orig CCY"/[Forms]![Summary Forms]![Spot])
Then I tried this, again failing:
Amount USD: DLookUp("Amount Orig Ccy / Spot Rate","Consolidated Reporting Table Orig CCY","Spot Rate =" & [Forms]![Summary Forms]![Spot])
Is it a syntax issue or will DLookUp not work for what I need?
Thanks for any help.
View 3 Replies
View Related
Nov 19, 2014
I have a parent form where I enter a value for tax rate. I need to use this value in the tax calculation in the subform fields i.e. subform.taxdue = subform.qty * subform.price * parent.taxrate.
I can get the value into the field. But I do not think I am putting it in the correct event. The parent form is a sales form and the subform is the sales items form. So there can be multiple items on the sub form.
View 5 Replies
View Related
Jun 17, 2015
I am creating a driving school database and have four different tables. Student, Instructor, Lesson and Lesson Type.
In the footer of my subform which works out what lesson type the student has taken and from which instructor, I have created a calculation to multiply the number of hours a student does to what type of lesson they take.
=([LengthOfLesson]*[cost]) (This works ok)
I then want to add all of these options together. I have tried:
=sum([OverallTotal]) in the footer (This doesn't seem to work)
and then
=[Booking].[Form]![OverallTotal] to show it in the form
The name of the subform is correct (booking) and name OverallTotal is also correct but I keep getting #error message.
View 3 Replies
View Related
May 12, 2015
I have a bound form to an invoices table. The form details all invoices raised, all payments made and an outstanding balance. The outstanding balance control is just a basic text box.
On form load, I go to the payment table and select all payments into a recordset, I then add up all payments and use the following calculation:-
Balance = InvTotal - paytotal
The Invtotal is the control on the form that has it's control source set as sum(invoicetotal)
The calculation used to work and the correct balance was displayed, indeed, for one particular client, the calculation works, but for all others, the summed value is blank and so the balance is effectively set to the paytotal value.
I know this is to do with the timing as the form loads and it's records/data are made available, as I have an update button that does exactly the same things as the form load and when pressed, all numbers work.....primarily because the summed invoicetotal value is now available in the form control.
I can see what is happening, but not sure why...I thought that form load gave you all records and would have populated the summed control before triggering the load event....obviously not.....
View 9 Replies
View Related
Oct 23, 2012
is it possible to calculate a values in a subform and main form. i thought it would be as simple as form to form but it doesnt seem to work.
ive tried this.
=[Forms]![frmDeparturesPaymentScreen-ItemListSubform]![txtTotalCost]-[txTotalPaid]
it just comes up with #NAME?
View 7 Replies
View Related
May 16, 2013
I need to create a form that using combo boxes selects a product-size-quantity, and then calculates total price.I asume that I create a query to make the calculation from the form, but for the life of me, I can't fathom out how to do it.
View 3 Replies
View Related
Jun 16, 2013
I'm pretty new to Access. I was wondering if it's possible to make a calculation for a specific row in an Access form.
I placed an example in the attachment (I made it quick in paint, because the actual Access form is in my language "Dutch" ):
Value1 and Value2 come from a database, so the amount of rows can change. When I click on "Calculate" on the first row, it should calculate 12 * 13 and show the solution only on row 1. If I click on "Calculate" on the second row, it should show the solution of 10 * 20 only on row 2.
I tried to solve this by using the following VBA code:
Private Sub Calculate_Click()
Solution.Value = Value1.Value * Value2.Value
End Sub
But if I click on the first "Calculate" button, it will fill all solutions with the first calculation (12 * 13).. The same happens when I click on "Calculate" on the second, third row etc.
View 2 Replies
View Related
Jun 7, 2013
In Access 2010 is it possible to have a tblKits with a column on it Reactions 8. Then on a form a field Used. When thee user inputs 2 into the Used field can the Reactions on in the tblKits be updated to 6 automatically?
View 1 Replies
View Related
