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?
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 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.
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.
Hello, I have the following criteria for my Query >=0.8*[Projects]![EstimatedHours]. This tells me when we've used 80% of our actual hours. How would I go about the criteria line if I wanted it to return above >= 50% hour use, but below 80%? Thanks
I have a query that is built up of 5 fields with only the 2 below being visible. I am wanting to be able to calculate the total price for each pole no i.e. Pole no 01-02 total price would be £1441.79, What is the best way to do this? Sample data is shown below
In qryAddticket, I am trying to calculate two conditions (see Condition 1 & 2)However, I am having a problem on the first condition because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one?
Condition 1: If the day_of_wk is 1, calculate time-reg_time/60, If the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24
Condition 2: If the day_of_wk is 2, calculate time/60, If the day_of_wk is 2 and time is less than 0, calculate time/60+24
My code: pre: IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60))
I need to read all the orderlines for an order into a record set and calculate the cost for each orderline. I need to use a loop in VBA to add them all together.
Im building a database for a car dealership. what do i need to do to take how many times someones name comes up in a certain field in a table and show it as a number. I need to include this calculation in a subfrom on a form which displays "employee info" which is already stored. Basically i just need to take how many times there name shows in the salesman field and show it as a number.
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?
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.
Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.
First I'm calculating the current Years of Service. SIV Date = Hired Date
Then I would like to calculate the years of service as of 12/31/"Current Year", where the Current Year would not require updating every year and 12/31 would be constant.
I'm just not sure how to specify the Month/Day and use Current Year not hard coded. I could set up a user defined date for that half of the calc but my users are not really bright and allowing them to simply click a button is my only option there.
Ultimately I will compare the 2 years of Service numbers to determine if there will be a change in the level of vacation accrual an individual receives during the year. The only part I have issue with is coding that date. Thanks for the help.
I am struggling with something im sure is quite simple.
I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.
What I need to do is crosscheck the calls answered by employee in both tables. For example:
Table 1 has 3 records for John. (This means 3 calls answered).
Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).
What I need is a report with both these sets of data and a new field with shows the the difference between the two.
I have a query field doing a simple calculation: 874*(18,3/55,65*0,0592) Access calculates this to: 17,0144948838454 Excel as well as my own calculator gets: 17,0144948787062 All table fields is defined as double.
If anyone can help me with an explanation as to why access doesn't seem to get this simple calculation right, I would be most thankful.
I have a query that ends with a final calculation, giving a typical answer of 0.033333333. I have created an append query, which works but doesnt pull the result of the calculation through, just displays 0.
In my report, there is a calculated column called Cost that display as $13.13. The orginal value is 13.125. This column is used to calculate another field called leftover.
The expected result is $16.00 - $13.13 = $2.87 The actual result is $16.00 - $13.125 = $2.88. So there is a increase of $0.01.
The results in my report is run using a query. In the query, I set the format of the calculated field, Cost as $#,##0.00;($#,##0.00). How can I ensure that whatever it calculate is what's displayed? Meaning to say if it display 13.13, use it instead of using 13.125 to calculate the leftover value.
hi, i have a query which retrieves data on products.
my problem is how to display a specific total i need.
there are three fields; [itemprice],[itemlabour],[itemdiscount]
the item price and labour columns are in currency format, but the discount column allows the user to select a discount between 0 and 50%.
now in a form, when i want to calculate the total price to be paid i have a simple code like this: [itemtotal] = ([itemprice]+[itemlabour])/[itemdiscount].column(1) the idea being, when a discount is selected, there is a hidden column in the combo which contains a specific number to divide the total of [itemprice]+[itemlabour], thus giving a total.
now i tried to use this code within a query, but it doesnt work.
First off, I want to thank ansentry for providing a solution to a problem I had (new record entry), and I apologize if reverse engineering the form you wrote for my own usage is considered bad form.
Ok, here's my issue. I'm designing a database that keeps track of materials mined in an MMO (quit laughing ) in this fashion: pick out a selected, user-inputted date rangepull all records that fall within the date rangeadd up the sum total for ALL users that submitted any minerals in the date range per mineral typeadd up the total for each mineral type for each person during the range (seperate from the total for everybody)run a calculation on each type for each person in the date range against the total of everyone, and output a percentage of what they mined vs. total Now, I'm pretty sure that I have to do this via a query or two. Or three or four, however many it takes. What I have little to no clue on is how do actually program the query(ies) itself to perform these actions.
Was wondering if someone could give me a few pointers/tips to point me in the right direction. Also, if you want to take a look at the DB, (and if you don't mind) post your e-mail address and I'll mail you a copy of what I have so far as it might clarify any questions.
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.
