Further, it looks like you are using a bound form which would mean you don't need to use the SQL update, a solution akin to Allen's example would suffice: On the form if you have a control for each of the fields involved in the calculation: Quite generic as you haven't given much detail but something like this in the After Update should work: Assume controls on the form are: txt Emp ID for employee ID, txt Hourly Rate for what to multiply the number or hours by, txt Hours Worked as what you input as hours worked Dim dbl Earnings as Double dbl Earnings = me! txt Emp ID & "," & dbl Earnings & ")",db Fail On Error I would probably look to record the hours worked and the hourly rate and calculate the salary on the fly rather than storing a calculated number. Edit your question and paste in your code and I should be able to make some Created an Employee table with the following fields : EMP Id , Name, Basic Pay, Total Working Days in a month, Actual Worked Days , Earned Salary.Or store the hours and total and calc the hourly rate if needed. Now i have created Form with all the fields in the Table and am calculating the Earned Salary using Control source using Earned Salary=Basic Pay/Total no of working Days in month* Actual Working Days.Each time you run the query, the table will be deleted and recreated.
The fields are labelled 2010 Amount, 2012 Amount, 2013 Amount and so on. =Nz([2010 Amount]) Nz([2011 Amount]) Nz([2012 Amount]) Nz([2013 Amount])" id="ctl00_m_m_i_ctl00_gr_ctl15_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5427572" / type.
At the bottom of the display I have put a calculated field to give us the cumulative total donated by that Trust. If it is a string then it would an empty string -- "" -- which would cause problems in a sum calculation.
I would prefer not to do a me.requery (a requery of the whole recordset). However, you would not see any NEW records that were added since you opened your form. Refresh() in that you will not change record position or see NEW records.
It will update your control, assuming the control is based on a query/table. This was perfect as it did not do a complete form refresh or requery. Refresh for either the underlying recordset, or for the bound control?
The Expression simply goes like this: =[2010 Amount] [2011 Amount] [2012 Amount] [2013 Amount] It works, but ONLY IF there is a figure for an amount in each field for the particular Trust. Thanks, Mike =Nz([2010 Amount],0)*1 Nz([2011 Amount],0)*1 Nz([2012 Amount],0)*1 Nz([2013 Amount],0)*1" id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5427386" / The Nz function is the Access version of the Is Null command in SQL Server. May your days get brighter and brighter, Jim P." id="ctl00_m_m_i_ctl00_gr_ctl13_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5427542" / In MS Access it is SELECT Nz([2011 Amount],0) as in SQL Server it would be SELECT Is Null([2011 Amount],0) You get the same result. May your days get brighter and brighter, Jim P." id="ctl00_m_m_i_ctl00_gr_ctl17_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5427693" / The assumption is that the field [201x Amount] is defined as a numeric data type.
If one of those yearly Amount fields is empty, then the calculated field displays nothing at all for that Trust. You don't have to do all the IIf statements." id="ctl00_m_m_i_ctl00_gr_ctl09_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5427445" / SELECT Is Null([2011 Amount],0) You get the same result. If it is a string then it would an empty string -- "" -- which would cause problems in a sum calculation.
Secondly, it seems you are storing a calculated field - Earned Salary is a calculation of Basic Pay, Total Working Days In Month and Actual Worked Days.
Generally storing calculated fields is considered a no-no, ref Allen Browne's page on calculated fields.
On this page Allen gives some tips on calculated fields that may help, especially if you decide you do want to store it and use Access 2010 (I'm only on 2007...).
As for your question, you still didn't tell me where your error was so it is hard to pin-point the issue.
I have two tables tbl Students1213 and tbl Attendance12-13.