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.
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?
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...
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.
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.
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:
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)
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 !
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.
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.
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.
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.
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.
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?
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?
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.
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
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
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.
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:
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:
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.
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.
