Forms :: How To Store Calculated Fields Into A Table
Oct 16, 2014
How to store a calculated field into a table
I am preparing an invoice and I have a field called deposit .Now this field calculates %40 of the total of the order .but if the user wishes they can enter what they like .Now this field must be stored in a table for ever and a day and must not change or the invoice will be useless .I can see no other way of doing this and my research tells me (allen Brown) that this is sometimes necessary .
View Replies
ADVERTISEMENT
Jul 20, 2015
I am trying to get a value from a textbox into a table I created...
The textbox "calculates a number based on which radio button is pressed example if radio button 1 is pressed it returns 0 if radio button two is pressed it returns 8 and if radio button 3 is pressed it returns 15." I need to save the 0,8, or 15 depending on which radio button is pressed into the table...
I know you aren't supposed to store calculated values into a table but the report would never need to be changed and it just saves a new one each time with that being said anyway to make this work would be very useful!
Also how can you use the answer of calculated textboxs in other calculations?
View 14 Replies
View Related
Apr 11, 2015
I have a form (frmFeeIncome) based on a table (tblFeeIncome)
On the form I have three fields which are FeesMonth, FeesYear, FeesUK (currency), FeesElsewhere (currency) and FeesTotal (currency). Each new record is entered like this
January 2015 25000 25000
What I need is for the total in the FeesUK field and FeesElsewhere field to be added up and stored in FeesTotal when FeesTotal has GotFocus
The FeesTotal has to be stored (I know it shouldn't be but it has to!)
I have tried the following in the GotFocus Event procedure but it does not work.
=[FeesUK] + [FeesElsewhere]
View 3 Replies
View Related
Jul 9, 2007
I was once told that Access does not like to store calculated values but I would like to.
Here is my expression: =([Shift Lenght]-[Total idle time]-[Total down time])/([Shift Lenght]-[Total idle time])*(0.33*[Total pc])/([Shift Lenght]-[Total idle time]-[Total down time])*([Total pc]-[Total scrap])/([Total pc]+0.1)*100
This gives me the value that I desire, now I need to store it in the table with the rest of the records data.
I am planing on storing 3 -4 expressions like this and then averaging them.
Then building a query to the averages for the day,week,month, and year.
Please be gentle I am not very familiar with access but learning a lot:)
View 7 Replies
View Related
Sep 9, 2014
I have form with student details on it and I want to add some additional information against that student (stored in a seperate table). I've created a form to input the data and a button on the main form to open the new form.
On loading the form I've brought with it the name of the student but I also want the UPN. I've done all the above fine.
What I need to do is then store the UPN in a field in the new table which is where I'm stuck.
What do I put in the Control source to make it store it. Currently I just have
=[Forms]![PupilPremiumStudents]![txtUPN]
View 9 Replies
View Related
Sep 9, 2006
Now I can create a Query that adds, subtracts a specific column, but im having a hard time trying to get the information into a form since it don't seem to allow me to pick the query field which is calculated as a datasource. Can someone tell me how to make a calculated query column appear on a form as a field? Such if I type in monday in the regular table, the form will add a few days to the next available game? I have the information/code already in a query that displays the query right in a new table using the Expr method but I can't get the form to just simply display the field. Someone please help!
I see a few people asked close to the same question but nobody never answered so I am thinking it is impossible
View 3 Replies
View Related
Apr 20, 2007
I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.
I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.
For example:
Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).
So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.
Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?
Thanx for any help!
View 2 Replies
View Related
Sep 14, 2006
I have a small Database that produces invoices, Each invoice could have 5 items on it which are compeleted at different Dates. (cutting hair in an OAP Home) Invoice to be produced at the end of each month which shows what happened each week. This will be generated for each person in the home. I can display all the information in a form when inputing the invoice information. I want a button to copy the invoice number the client name and the total of the invoice to a new table. The total of the invoice is calculated control on the form.
How do I do this??? can anyone help
I need a total in a table to substract payments from.
All Help will be much appreciated.
View 1 Replies
View Related
Apr 26, 2007
Hi all,
I need to figure out how to have two calculated fields written to the database table instead of just being displayed in the form.
The user selects a number from 1 to 5 in the first field, and a number from 1 to 7 in the second field. The first calculating field multiplies these two numbers and displays the result. The second calculating field determines where the resulting number fits in three ranges of numbers and displays the result as Low, Medium, Or High.
My problem is the calculated results are be displayed in the form but not being written to the table. I cannot create a report to display all the High results, etc.
How do I update the calculation field formula to write the results into the table so I can create queries/reports on the calculated data?
Thanks,
bugleboy62
View 3 Replies
View Related
Sep 27, 2012
I have three columns where A is a number, B is a number and I want to make C = to the greater of the two. i.e.
Col A = 10, Col B = 12 therefore Column C = Col B or Col A = 12, Col B = 10 therefore Col C = Col A
View 4 Replies
View Related
Dec 5, 2014
I am trying to see if it is possible to store colors in a form or table and then reference them while in VBA. What I am hoping to do is when I write all my code for command buttons to change On Got Focus, instead of writing xxx.backcolor = RGB (255,255,255) i could do something like xxx.backcolor = Forms!HiddenColors!Command That way if i want to ever change the color scheme of the db, I can change it in one place rather than hunt lines of code.
I have played around but with no success. Technewonline is a website that specializes in introducing the latest technologies such as Best Tablet Android Have Price Under $200 and Best tablet of Apple in 2014 and The Best Midrange Smart Phone In 2014 and Top Best Ultrabook Of 2014 and The Best Phones 4G Valued At Under 300 USD is also a website for sharing your tips about computers, mobile phones and tablets, products are available from leading supermarkets will surely satisfy you.
View 1 Replies
View Related
Aug 27, 2014
I managed to count the performance of users. The only thing I need to do is to put the names of the users on the form (under different tab) so I can select them and they show their performance. The best would that they are showing up there in the form till I change the names. I use now a text field to write the name, but when I close the database and open, I need to add the name again.
If I could use somehow the names from the users stored in a table, that would be great.
View 4 Replies
View Related
Apr 7, 2014
I have a form where the record source is a table.The form has about 5 calculated fields on it. The screen flickers quite badly and at the bottom says calculating. Sometimes the database locks up.
To stop this flickering i created a query so that the calculations are done in the query. Then i linked the form to the query instead of the table. Am i doing this correctly as i didn't think the table would show any new data that i entered into the form but it does seem to work. Is it because i am only using one table.
I am using Access 2003.
View 6 Replies
View Related
Nov 29, 2005
I have a form created from a table and I had to use calculations in some of the fields to automate certain processes. Now, if I pull a report based on the fields in the table - I am getting all except for the fields with the calculations. Is there any way to update the main table - since most of the calculations were done with text boxes?
View 1 Replies
View Related
Aug 28, 2006
I need to display a value from a table in a text field on a form based on another text field on the same form.
The table has records as follows:
Month/Year xx Month No xx Year No xx Month ID (xx shows break in columns)
Jan-06 xx 1 xx 2006 xx 24
Feb-06 xx 2 xx 2006 xx 25
Mar-06 xx 3 xx 2006 xx 26
Apr-06 xx 4 xx 2006 xx 27
May-06 xx 5 xx 2006 xx 28
Jun-06 xx 6 xx 2006 xx 29
Jul-06 xx 7 xx 2006 xx 30
In one text field I use the DateSerial function to calculate the value of the previous month (e.g.Jul-06). I need to return the MonthID value to the second text field based on the value stored against it is the table, so in case of Jul-06 it would show 30 in text field 2.
View 1 Replies
View Related
Aug 5, 2006
Is there a way to make some of the fields from my form that contain calculated values carry to the table? Right now the values calculate correctly in the form but when I look at the table the field is blank...
I was hoping to use one form to calculate a value, insert it in the table, and then use that value in another form.
View 6 Replies
View Related
Aug 8, 2014
I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:
1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill
Here are my excel formulas that for the life of me I can't get to work.
Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))
Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))
Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO") ,"NO"))
Can I have calculated fields that refer back to a calculated field?
View 10 Replies
View Related
Jul 26, 2012
I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.
Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.
I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:
=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]
(Later I will add in all the iif's for if there is no preferred name)
Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.
I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.
View 2 Replies
View Related
Jan 30, 2014
Why can't I ever get these to work properly? I want to use them to ignore null fields so that I won't get the punctuation but it isn't working.
Here is what I have but the commas still show up:
=IIf([Status]=1,(+[MailBoxOrPOBox]) & (+", "+([CompanyLocID].[Column](2))) & (+" ,"+([CompanyLocID].[Column](3))),"")
is there a better way than trying to use these + signs??
View 14 Replies
View Related
Apr 17, 2015
I'm still very new to Access.I have a form with subforms.Some of the fields are calculated fields.I cannot figure out what some of the fields will not update automatcally. Once I click out of the form and then back in, it will update.Tried many things like, Refresh, Requery both on the form and on the text box(es).I wish I could figure out how to attach my test database to show you.
View 11 Replies
View Related
Jan 13, 2014
In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.
I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.
So how to format this as a number field properly?
Also for any extra information:
- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box.
- The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.
View 4 Replies
View Related
Aug 14, 2015
i have a continues form that lists all my active items and I'd like to use it to quickly check if they have all the data inserted, ie entries in each separate table. it looks like this
I perform a simple check on form load chk = DCount("[phID]", "preh", "[phbID] = " & Me.tID) where me.tid is the item ID and this gives me 0 or 1 and writes ok, or missing into the appropriate box.Problem is it tkaes value for the first item and repeats it for eah other, so I get ok or missing throughout the continuos form.How can i force a check for each line?
View 1 Replies
View Related
Mar 6, 2006
:rolleyes:
Please help! I am a newbie and VERY confused. I am not a programmer but am just learning so any help much appreciated! I'm not even sure if I've posted this to the right place?
I have so far created a table and also an asscoiated form - they both work! Changes in the form go into the table, no problem EXCEPT where I have a calculated field in the form - this info doesnt go into the table - how do I get this information to go back into the table?
I am trying to do this because I want to include the result of the calculated fields in a mail merge letter in Word (and Word wont pick up fields from my associated form - only from the table or from a query!)
Please rescue a damsel in definite distress (I've been knocking my head against a brick wall on this for 3 hours now!)
Many thanks in advance :D
View 5 Replies
View Related
Jul 28, 2005
I would like to know if I can control what field the check box enters the -1 value in my record depending on a value from another field in that record.
In my table called workbook I have fields called, phone type which is a text field, wall and wm entry which are both yes/no fields. I want to put one check box on my form to check when a wall mount is needed for the device. The thing is there are two different wall mount sizes. So I need to check the phone type field on my form, if the value is ENTRY then I need to have it enter -1 in the WM Entry field. If the field anything other than ENTRY then the -1 value should go in the WALL field.
Can this be doen and how do I code it?
Thanks in advance
View 4 Replies
View Related
Mar 12, 2014
I have calculated files in a form which is summimg the working hrs of each employ�es for a particular data.
I am able to show the same in the form but want to add this value in the table.
Is is possible to add this data from the form to the able?
View 1 Replies
View Related
Mar 19, 2013
I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format
The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.
Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.
View 3 Replies
View Related
