Calculated Fields Not Writing To Table
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 Replies
ADVERTISEMENT
May 22, 2006
Hi Guys, I don't know Access very well but I know enough to generate a 2 dimensional database. The problem I have is that I have been asked to look at a travel agents database to create some additional letters. The problem is that they use a form which has an underlying data table called customers. When they create a record in the form all fields in the customers table are filled in except those on the form which derive their data by calculating two or more fields i.e Date Due is [Date of Travel]-70 (days), Balance Owing is [Cost]-[Deposit]. None of these derived fields fill in their corresponding fields in the customers data sheet.
I would welcome any ideas please. Thanks in anticipation
PompeyFC
View 3 Replies
View Related
Jun 6, 2013
I am trying to write some fields to an access table definition. It is working for the most part except that I want to write some of the fields as dbText and others as dbDate. For some reason it is writing all of them as dbDate and I don't know why.
Code:
additionalColumns = Array("a","b","c","d","e","f")
'add additonal columns to report
For i = LBound(additionalColumns) To UBound(additionalColumns)
MsgBox additionalColumns(i)
If additionalColumns(i).Value = "a" OR "b" Then
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbDate, 10)
tb1.Fields.Append FieldName
Else
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbText, 150)
tb1.Fields.Append FieldName
End If
Next i
View 3 Replies
View Related
Nov 3, 2014
I concatenated 2 fields CR_Numbers: = CR_Number & [Sub Number] to display 12.01: from 12 and 1. I am now wanting to lookup the CR_Numbers to edit or add information into other fields.
I want to use a pop up form to edit/add data without having the form add an entire row to the table. How do I reference the Table ID so that it will go to the equivalent row to add the data in?
Main Table = Change Request
Email Table = AORB_Email
Fields in Form!AORB_EMail: Change Request (CR_Numbers), Priority (Pri_Name), Hours (Hour)
I plan on using the Priority and Hours in the email to set some dates and expiration times as well as in the subject of the email. I have figured out the email setup, and if I manually fill in the Priority and Hours fields everything works as it should.
View 14 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
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
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
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 4 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
Apr 14, 2015
I have a table linked to SQL Server 2014. As SQL Server 2014 does not support calculated fields I created a query to use formulas. Now I want to write formulas on the existing fields ( TotalMarks ) of table Not to create new fields.
View 1 Replies
View Related
Jan 14, 2014
Access 2007-10
Listbox created: List62 (I know I need to rename it, but for now)
Multi-select: Extended
Row Source Type: Field List
Row Source: qryFieldList
Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).
Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:
Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If
[code]....
It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.
View 8 Replies
View Related
May 29, 2014
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
View 6 Replies
View Related
Nov 17, 2006
Hi,
I am fairly new to msaccess programming and am trying to build a stock control system for my business.
I am happy with everything I have done so far but there is one thing I cannot figure out.
I have set up so that the user can click a button and remove the relevant number from the stock level. The buttons are for removing 1, 5, 10 or 20 (there are reasons for these numbers). However I would also like the button to take information from the form (which is referenced to a query) and write that information, along with an autogenerated id number and the current date/time to another table. This is for our record keeping, so that we can record what is selling well etc and when the last one was sold.
How do I do this?
I have gone through the help files and my books, but I think the problem is I don't know where to start looking.
I also want to add a button that will add items to an order table. Again by taking the information from the form and writing it to another table. With this one a message will appear asking for the quantity to be entered. It will also check the order table to see if the item is already there and advise if it is.
Any help gratefully received.
Steve:confused:
View 3 Replies
View Related
May 17, 2005
I hope this makes sense?
I have a main form with a button that opens a new form. On the new form I have 5 fields. 1 of those fields is the Employee ID which I have the form getting from the main form (IE the Control Source is =Forms!Search_Employee!Employee_ID )and the other fields are bound to my table for the new form. I am unable to get the Employee ID to show in my table, it is not updating or writing the value to that field. Not sure if there is code or how I should or can get this field to write to my table. Any Ideas or be very appreciated. Thanks anyways guys I igured this one out. I was able to set the dafault value to point ot my main form so that my Control Source could point to my table and it worked.
View 1 Replies
View Related
Oct 18, 2005
Hi,
I have a check box that gets ticked when 'work complete', it adds the parts/service totals and fills in the 'parts total', 'vat' and 'total' fields.
This works fine but I need it to write the values to the table as well rather than just displaying the values in the fields so reports can be produced etc.
Anyone know the easiest way to do this?
Thanks
Housey
View 5 Replies
View Related
Dec 8, 2006
Hi all,
wonder if anyone can assist me with this the SQL Server
security/write (?) issue. The SQL Server Admin has created a table for our Dept. to use and via SQL Server I can get access to it using the username and password she supplied to me. I can also link to this table in Access 2002 via ODBC which is the way I am using to write to SQL server tables. I have developed an Access application to allow one of our staff to be able to write data to the SQL Server table at a click of a button, however when I open the table the 'add new record' button is greyed out, I can not add any data to the table.
I explained this problem to the Admin as well as the way in which we access the table and she is certain that the problem lies in Access but i'm unsure how that can be the case.
Has anyone experienced this problem before ? Is there a fix and if so how is that implemented ?
Should I also post this in the SQL server forum ?
Thanks in advance,
Mitch....
View 3 Replies
View Related
Jan 16, 2013
I'm using Access 2010 and am trying to pass a variable in a form field and store it in a table i.e. I have a customer database with various fields: Customer_ID, Customer_Name, Customer_Address etc. After selecting a particular record i.e. Customer_ID = 20 l need to store the record number '20' in a temporary table. I presume that l could then use Dlookup to read the info back at a later stage.
View 7 Replies
View Related
Mar 23, 2006
Can anyone tell me what's wrong with the below statement ? I'm trying to pull the contents from a text box in a form. The text box is supposed write the contents to a field (memo data type) in a table. Thanks ! ! !
strComments = Forms![Payroll History]![Comments].Column(0)
View 14 Replies
View Related
Sep 21, 2011
I need to create a table with names of controls on my forms.
I'll use that table to apply user rules.
Any solution to write the table automatically using VBA code ?
I know how to populate a combobox...
DoCmd.OpenForm YourFormName, acNormal, , , , acHidden
Dim c As Control
For Each c In YourFormName.Controls
[Control-Name].RowSource = [Control-Name].RowSource & c.Name & ";"
Next c
..., but not how to write a table.
View 1 Replies
View Related
Jan 5, 2005
This is the first time that I have done any major work with forms. After I thought I had finished a problem came up. There are several forms that are use to input information into a table. When the form is opened it grabs an automated number for tracking. The problem is, that if the form is opened and then closed it creates a line of data with all null values.
What I would like to do is have the form open, the user fills out the information, and upon pressing the "save" button, the data will save to the table. I think I need to have each text box write to a variable and then save on the click event. Or I could be completely wrong and need to do something else.
i'm guessing this is something I would learn in Forms 101 if i had ever taken that type of course
Thanks
View 7 Replies
View Related
Dec 1, 2014
I am using Access 2010. How do I prevent the object typed into the textbox on a queryform being written to the table. The result from the name typed into the textbox on my query form correctly produces the result from the query, and my macros then produce the correct report, which I can either print or close due to the controls in the heading of the report. However, when I view the table, the name typed (only) has been inserted into the correct field as a new record in the table. Is there a macro I can add (I assume to an event in the query form) to prevent this happening?
View 8 Replies
View Related
