Problem With Text Box Calculation

Feb 9, 2006

Hi all,
I have a form tha is based on a table and a subfrom on another.The two tables have a valid 1 to many relationship and all is well record viewing wise.
Now I have some financial data like the fee that was agreed upon on the main form on a text box .On the subform the user can enter the payments made on variou dates.
What I want is to be able to have a text box on the main form that will do the subtraction (fee - all the various payments) and display that on the main form.Is this possible cause I cant seem to be able to do it and if it is not, is there another way for this to be done.
Thank you very much for your time :)

View Replies


ADVERTISEMENT

Text Field That Needs Calculation

Apr 16, 2008

So here is my problem i am semi access savy but i have having trouble with one thing. At work when we make a schedule there are 5 possibilites for how you want to mark your day. I have created a field using text as my parameters since my possibilities include both letters and numbers. At the end of each week I need to create a calcuation only utilizing the number which is 1 to add up the total shifts in the week.

I have tried some IIF statements with no luck. The 5 possiblities are 1, c,r,a,l. Each of the letters stand for something. When the scheduler runs the query he needs to see the letters or the number for each day. But i can't create a properly working calculated field for adding all of the 1's together only. The letters don't need to be added. Any ideas?

Also I have to run a sum for the total of each day at the bottom so I am running into the same problem there if there is any letters in the boxes. Going to a number field is not an option
thanks for the help

View 6 Replies View Related

MS Access: Need Help W/ Calculation In Text Box

Jul 12, 2005

Could someone please help me with a calculation expression? I want the calculation to be in an unbound text box on the main form ([Primary View])which should calculate the sum total all records shown in the subform that contain a calculated field in the subform ([CBR Data Subform]!Rentable). The subform is set up in the main form to show records that pertain to one record in the main form. Right now, I have the following shown in the textbox:
=([CBR Data Subform]!Rentable) and I get a result but its not what I need.

I have tried:
=Sum([CBR Data Subform]!Rentable), &
=DSum([CBR Data Subform]!Rentable)

and I get error messages.

What other expression can I use or how can I improve the expression?

Thanks,

View 9 Replies View Related

Variable For Holding A Calculation For A Text Box

Oct 8, 2005

Hello,
I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form.
My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table.
Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.

Calculation in the text box control source=
[Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.

It was suggested to me to have a Variable hold the calculation, I’ve been trying to write a variable but nothing seems to be working, I’ve been trying to place this variable I made into the after update and before update of the text box Nothing works I get all 0.00 in the form header text box, Here’s the code for the variable I made, can some one see if it looks correct.

Dim calcVariable
calcVariable = [Tot25yrcomp] + [Me.feltstot]
mat25yr = calcVariable

I was wondering if I need to say Me.mat25yr = calcVariable

Thanks--Any help will be greatly appreciated.

View 1 Replies View Related

Variable For Holding A Calculation For A Text Box

Oct 8, 2005

Hello,
I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form.
My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table.
Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.

Calculation in the text box control source=
[Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.

It was suggested to me to have a Variable hold the calculation, I’ve been trying to write a variable but nothing seems to be working, I’ve been trying to place this variable I made into the after update and before update of the text box Nothing works I get all 0.00 in the form header text box, Here’s the code for the variable I made, can some one see if it looks correct.

Dim calcVariable
calcVariable = [Tot25yrcomp] + [Me.feltstot]
mat25yr = calcVariable

I was wondering if I need to say Me.mat25yr = calcVariable

Thanks--Any help will be greatly appreciated.

View 1 Replies View Related

Query Calculation Depedent On Form Text Box

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

Forms :: Using Unbound Text Boxes And Calculation

Nov 12, 2014

Got a database that is working great

1 table - Employees
2 Queries
1) - QEmployees Extended
2) - QyrsEmp

2 Forms
1) - Employee Details (source control is "QEmployees Extended")
2) - Employee List (source control is "QEmployees Extended")

in Form "Employee Details" there is field named "HireDate" this field is also found in the table (records) "Employees" and "QEmployees Extended".When Form "Employee Details" is ran the "HireDate" appears as it should in the "HireDate" text box.

What I want to do is add a unbound text box to Form "Employee Details" named "txtYrsEmp" (Years Employed). and perform a calculation that will take the "HireDate" (date) and compare it to "Todays Date" and come up with the number of years employed rounded by 2 and show this result in the unbound text box called "txtYrsEmp"

Now I did a Qurey just to see if I could calculate what I wanted- "QYrsEmp" where in the first column I entered in the Field row (top) Expr1: EmployeeID from table Employee and in the second column I entered in the Field row (top) txtYrsEmp: Round(DateDiff("d"'[HireDate],NOW()/365.25,2))

Now the query returns the exact results I want so I know the calculation is possible at least here in the query.

View 7 Replies View Related

Forms :: Unbound Text Box Calculation Using Two Other Unbound Text Boxes

Jul 25, 2013

I am running in to a brick wall with this. I have an unbound text box with the control source set to =IIF([text42]=0,0,[text42]/[text44])*100 and in continues to return a #name? error.

I am not sure how to get this expression to work. I have even tried to put =[text42]/[text44] and I still get the #name? error.

View 3 Replies View Related

Text Boxes Control Source = Calculation Can't Save Sum

Oct 7, 2005

Hello,
I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form.
My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table.
Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.

Calculation in the text box control source=
[Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.

Thanks--Any help will be greatly appreciated.

View 1 Replies View Related

General :: Text Box Calculation - Main Form And Subform

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

Adding Calculation Field (text Box) To Assets Template Form

Apr 3, 2013

I am trying to add a calculation field "Status" (text box) to Assets template form i.e

IF Asset item = X
Status = present

But am getting #name? in the text box

View 3 Replies View Related

IF Then Calculation

Oct 11, 2005

Trying to calculate a few fields. 1st field is [delays] and 2nd field is [Planned]
The Plan scheduled for 60 and the Delays are an entered value. In the on update command button on the form I want code that will take the value above 60 and add then to the value in the [Delays] So if delays is 10 and the planned is 70 then planned will change to 60 and delays will = 20

any help

Jon

View 2 Replies View Related

Q:Calculation

Oct 24, 2005

Hi All.

I have 3 tables in Access:
tblCurrency (Fields: IDCurrency, CDate, CRate)
tblOrder (Fields: IDOrder, OName, ODate)
tblCalculation (Fields: IDCal, Rate1, Rate2, Rate3, COrder)
IDOrder is related to COrder with "One to Many" connection.

I have Form frmOrder, based on tblOrder. For Example Form has 1 record:
IDCal OName ODate
1 XXXX 2005.10.24

For this record related table has 2 records:
IDCal Rate1 Rate2 Rate3 COrder
1 10 3.5 15 1
2 6 4.2 12 1

tblCurrency for Exapmle have one record too:
IDCurrency CDate CRate
1 2005.10.24 0.2

In frmOrder I have textBox txtRate. Here I need to be calculated:

=Sum (Rate1*(CRate from tblCurrency Where tblCurrency.Cdate = tblOrder.Odate)+Rate2*Rate3 for Each record related to tblOrder).
By Exapmle: (10*0.2+3.5*15)+(6*0.2+4.2*12)=106.1

Please help with expression.
Thank You in advance.

View 3 Replies View Related

Need Help With Calculation

Jun 9, 2007

hey i hope some one can help me...

I am making a database for products and suppliers. Many suppliers can supply each product.

The product details are in a table called products, the suppliers details are stored in a table called suppliers and the suppliers offer for each product is stored in a table called prices.

the structure is as follows:

Table: Products
Product Name
Trade Price

Table: Suppliers
Supplier Name

Table: Prices
Product Name
Supplier Name
Supplier Offer

I have form that shows the product details and a subtable linked to the table:prices.

The problem

I need to add a field in the subtable called "Percent off trade" which is the trade price - supplier price / trade price * 100

i created a query showing the same records as the prices table plus the field "trade off price" and set the Field to:

Percent Off Trade: ([Forms]![Products]![Trade Price]-[Prices]![Supplier Price])/[Products]![Trade Price]*100

But this doesnt give me the correct percent :confused:

I hope i have explained this query and would really appreciate anyone who takes the time to suggest anything that could help

thanks in advance!

View 5 Replies View Related

Calculation

Nov 21, 2007

How can I multiplicate two numbers from two columns in a subform, show their result at the end of the row and then calculate sum of those results on the main form. This is something what I know how to do but the problem is following:

The total sum on the main form should be multiplicated wit 0,5 % and then reduced for a value of a specific row sum. It should be something like this: (Sum of rows sum * 0,5 %) - Sum of the specific row = Total sum on the main form. And all of this should be on a report too.

View 1 Replies View Related

What Calculation To Use?

Jan 2, 2006

What would I use for a formuls to find all product that are in stock.
like I have a price for all products that i stock, but some i don't have in stock. How would I query for the ones that I have in stock?
In my table I have it as UnitsOnHand, I have it as how many, like 1 or 3 or 4 etc. And also a field for Paper or Metal.
How would I query just for paper and not metal?
I am also going to need a query for the lowest price and the highest price.
Please understand I am New and I have allready search through all the post and can't seem to find anything close to what I am trying to do.
Any and all help would be great

Thank You
WackoWolf

View 10 Replies View Related

Need Help In Calculation

Jan 20, 2008

hi,


i'm working in a database and i face some problems in some calculation

this is my table which contain these fields:
NOC number
Company Name
Warning Letter


Note: each company has many NOC

Here what i need from the query

1- create a field which contain a total of warning for each month
2- create a field which contain a total of warning for each NOC
3- create a field which contain the total of warning for the company

View 3 Replies View Related

Help With A Calculation!

Feb 26, 2008

This is probably an easy calcualtion I am overthinking...
I am trying to make a query where I am calculating the "Total Value" of each customer's order of each item for the current week. The item "my company" is selling is fish, sizes small, medium and large all at different prices. I'm not exactly sure what they mean by "Total value", but I assume I take the cost times the quantity...but then what is the difference between Total Value and total ordered (in dollar amount)? Any suggestions?

View 2 Replies View Related

Calculation

May 25, 2005

I wonder if someone can point me in the right direction with a calculation problem. I have never done calculations in access before but want to make a balance sheet for a club account that I am designing. Very much in its trial stages, I have a form with three currency text boxes in it, set up as continuous. MoneyIn and MoneyOut are in the main form whilst Total is in the footer. All I want is to be able to put in a value in MoneyIn or MoneyOut and for the Total to update. Problem! I dont have a clue where to start! :rolleyes:

View 2 Replies View Related

Calculation Help

Jun 20, 2006

Here is a part of my database nicely zipped for you guys to help me with.

whats happening is when i click the totals button whatever the highest cost option that is selected with a "Y" should be displayed in the totals text box.

example:

Installation of heating system - "Y"
Replacement Kitchen and Bathroom - "Y"
Total should be "£90"

The code behind the button works fine with every choice but not the installation of the heating system. When this is selected "Y" the code seems to be ignoring it.

Any help will be much appreciated.

View 4 Replies View Related

Payroll Calculation

Dec 6, 2005

I am trying to type a code based on 1 & 2 criterias. However, I am having a problem on the first criteria because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one? Thank you very much!

Criteria 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

Criteria 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))

View 4 Replies View Related

Time Calculation

Jan 25, 2006

i have two date/time fields

sdate (start date) 01/01/2006 14:30 - format(dd/mm/yyyy hh:mm)
edate (end date) 02/01/2006 14:31 - format(dd/mm/yyyy hh:mm)

i need to get answer in HH:MM (24:01)


pls help me

thnx best regards
ibu

View 4 Replies View Related

Time Calculation

Jan 25, 2006

i have two date/time fields

sdate (start date) 01/01/2006 14:30 - format(dd/mm/yyyy hh:mm)
edate (end date) 02/01/2006 14:31 - format(dd/mm/yyyy hh:mm)


i need to get answer in a textbox as 24:01 (hh:mm)


pls help me

thnx best regards
ibu

View 2 Replies View Related

Time Calculation

Feb 8, 2006

Hi all,


I'm a beginner, and i want to know how to calculate time difference. For ex, if i substract 23:45 of 02/09/2006 with 00:10 of 02/10/2006 then i get a - "ive" value... Please somebody help me with a vba code for this...

thanks
Senthil.

View 1 Replies View Related

Date Calculation

Feb 11, 2007

I want to resolve date calculation problem.

I have one form field with users total sum of lenght of service (format of field looks like yy/mm/ddd) in another form field I have data with users date of employment in a firm.
In the third field I want to show total sum of lenght of service (previouse lenght of service and lenght of service in our firm) in format like in first field yy/mm/ddd.

What is a easiest way to calculate a third field in a form.

Thanks in advance !!

View 3 Replies View Related

Speed Of Calculation

Apr 5, 2007

Hey,

I noticed something strange in access 2000: sometimes it takes a long time to calculate a report and other times it goes rapidly. I don't see any process taking a lot of CPU %.

When i do the same thing in Access2003, it goes rapidly every time.

Can anyone help me?

Rik

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved