Form Footer Calculation Error
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 Replies
ADVERTISEMENT
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, 2007
Hello. I think is going to be very challenging for someone to assist with. In Access 2003. I hope this makes sense.
In a grouped(?) footer of a report I have the following calculations in text boxes, and they are working well:
Column1: =Sum([pays])
Column2: =Sum([fuel])
Column3: =Sum([osa])
Column4: =(Count([fit(i)])+Count([ag(i)])+Count([azg(i)])-Count([loc_id]))/4
Column5: =Sum([pays])+Sum([fuel])+Sum([osa])+(Count([fit(i)])+Count([ag(i)])+Count([azg(i)])-Count([loc_id]))/4
Column5 is the grand total of columns 1-4
In the report footer I have the following calculations going on, and they are working well:
Column1: =Sum(Abs([main Zone]=1)*([pays]))
Column2: =Sum(Abs([main Zone]=1)*([fuel]))
Column3: =Sum(Abs([main Zone]=1)*([osa]))
Column4: (blank)
Column5 (blank)
My problem is this: I would like Columns 4&5 of the grouped area to carry to Columns 4&5 of the report footer. I can get Columns 1-3 to carry to the report footer, but not Columns 4&5. Again, Column5 being the grand total for that zone. If I try to combine the count expression with Sum(Abs(... expression, I get 'Cannot Aggregate...
Thanks in advance.
View 5 Replies
View Related
Oct 22, 2013
On a form I have the following field:
Name: PaymentsMade
Control Source: =DLookUp("SumOfPrePaymentValue","qryInvoicePrepaym entSum","InvoiceId = " & [InvoiceId])
In the form's footer I have the unbound text control:
Name: SumOfPaymentsMade
Control Source: =Sum([PaymentsMade])
SumOfPaymentsMade returns #Error
How do I get it to return the sum of PaymentsMade?
View 4 Replies
View Related
Aug 25, 2006
I have some reports which lay my data out perfectly.For example,Report headerPage headerDetailsPage footerReport footerNow, I'd like to have exactly the same layout on some forms but with various buttons, etc. for manipulating data.The only problem is that when I put something on the form footer, it appears at the bottom of the viewable screen area no matter whether the detail area should overrun or not ?Is this due to form size or something else?Ideally I'd like to have it appear right at the bottom of the form and allow the user to scroll down the form to see the footer.
View 6 Replies
View Related
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 1 Replies
View Related
Sep 13, 2004
I'm using this date format in my report footer.
=Format(Date(),"Long Date") & " " & Format(Time(),"Medium Time").
It gives the current date and time on my computer, but causes an error on the other computers in the office.
Any ideas why?
View 7 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
Aug 16, 2006
I apologise if this question has been answered elsewhere but I have tried looking and can't find anything that helps.
I'm in the process of upsizing an access database to an adp front end and a sql server back end. One of the forms in my original database was a continuous form with some calculated text boxes in the form footer. One for example had the formula
=Sum(IIf([field1]="valueA",1,0))
which worked quite happilly.
I've now changed my forms recordsource to a stored procedure which works fine, however none of the calculated text boxes in the footer of the form work. I have read that complex expressions might not work in adp's and that I should use CASE instead of IIF, which I've done. I've taken the iif part of the formula and added a calculated field to my underlying query so all I need to do in the calculated text box is an =sum([aField]) but I still get a #Error.
Even if I do a =count(1) or something really simple like that I still get #Error.
Can anyone see where I'm going wrong?
View 1 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
Jun 23, 2014
In Form Design, my form is fitting properly; however, when I select Form View, the form overextends as seen here:
Form View:
Design View:
The footer is not extending all the way in Form View. I've tried fitting to grid, resizing it manually, and a couple of other things.
There has to be a quick fix for this right?.. Forgot to blur out other Logo.
View 2 Replies
View Related
Jan 14, 2008
I am having a small problem when using the Sum function on the footer of my form. I am using the following:
=Sum([UnitQty]*[UnitCost])
UnitCost is a currency and UnitQty is a number
This is working except for one senario. Sometimes the quantity is a decimal. For instance, 27.75 X 0.5 = 13.875. It appears that when the sum is totaling this, it does not round up to 13.88 like I need it to. I also tried the following with no luck:
=Sum([UnitQty]*Round([UnitCost],2))
Any ideas?
Gregg
View 4 Replies
View Related
Mar 12, 2014
I have a form with three fields named 'quantity' (numberfield), 'service' (combo box) and 'amount' (calculated field with controlsource =[service].[Column](2)*[quantity].
I try to sum the amount field in the formfooter with =Sum([service].[Column](2)*[quantity]) but I receive an error.
Somehow it does not get the value for [service].[Column](2)
How can I do this?
The table for the form is called customer_detail
View 2 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
May 19, 2014
My form is setup with Continuous view, with data feeding from a table. The structure is straighforward (excel style). AgentID, workHrs, holidayPaid, holidayUnpaid, sickHrs etc. The structure is not normalized, and I know the risk. This is basically only for human interpretation, not really a structure that will cause instability or patched up working of the DB.
I have the continuous form view, so it can be viewed in one single display, I have placed a Totals at the footer, which is populated by nothing but a simple = Sum(columnName). This works great in most of the computers, except one. My boss's computer. Which is where it actually needs to work. I am baffled, because I have tested it on almost every single computer in my office, everyone's work. Just his computer is playing up. I am unable to pin point the problem. The status bar keeps going "Calculating..." but even after giving it a solid 5 minute it does not seem to work !
View 5 Replies
View Related
Apr 6, 2013
I have a form that is displayed continuously and there is a text field in it (Text12).In the footer of this form I would like to add a textbox that is the sum of all Text12's on the form.I tried =Sum([Text12]) in the textboxes control source but that is just displaying an error when I run the form.
View 2 Replies
View Related
Oct 23, 2014
I am trying to create an invoice report. I want to add all the costs from the detail section in my footer. I have accomplished this in either the Report footer and/or the page footer, but the problem is that the report footer doesn't go to the bottom of the page, and the page footer doesn't go to just the last page. I have tested a bunch of suggestions that don't seem to work.
1. Print page footer with Report header = NO - only works if your report is two pages or more.
2. Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
If Page = Pages Then
Me.[TextBoxName].Visible = True
Else
Me.[TextBoxName].Visible = False
End If
End Sub
The Page functions worked, but it did not change the results from page to page. If it was a two page report, then the if statement is false and the text box was not visible on page 1, but it also was not visible on page 2.
View 1 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
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
