I want to do a calculation in the after update event of a field on a form. The calculation is on a field that is on the form from the table the form is based on with another field from a query. The only hitch I have is with the syntax that calls out the field from the query. Ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
calculation of two fields from different sources
- Thread starter shelspa
- Start date
gusbrunston
Programmer
Hi.
Why not design a query to use as the Data Source of the form, include both fields in the query and do the calculation in either the query or on the form?
Are you trying to save the calculated value? Gus Brunston
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com
Why not design a query to use as the Data Source of the form, include both fields in the query and do the calculation in either the query or on the form?
Are you trying to save the calculated value? Gus Brunston
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com- Thread starter
- #3
If the one field is greater than the other I want the calculated field to be equal to 1 else it will be equal to 0. I do want the value saved to the table. My problem is I don't know how to do an if statenment in a query and save it to the table so I'm doing the calculation in the form. Going this route, I don't know how to refer to the one field that is only present in a query.
gusbrunston
Programmer
Ah! The elusive field that's only available in the query is a calculated value.
The following is not the answer you want:
1) There are exceptions to the rule that prohibits saving calculated values. Is this one? (See Rules of Normalization) It's likely you should recalculate whenever it's necessary on a form or in a report. Then, if the underlying value data is edited, the form or report will reflect the most recent result of the calculation.
2) If I were going to compare the two fields in a query, I’d add a result field to the table source of the query and call it “ValueResult”. I'd select the two value fields (from a table or from tables and/or queries) as well as the third (result) field. In the field row of the query I’d enter:
3) If I were going to calculate a value on a form I'd want a control for both fields on the form. I would do this with a query selecting both fields (from the same or different tables and/or queries). The query would be the data source for the form. The controls would be bound to the two fields in the query. I would put an unbound text control on the form called “txtResult”. Then in the after update event of both of the value controls:
Gus Brunston An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com
The following is not the answer you want:
1) There are exceptions to the rule that prohibits saving calculated values. Is this one? (See Rules of Normalization) It's likely you should recalculate whenever it's necessary on a form or in a report. Then, if the underlying value data is edited, the form or report will reflect the most recent result of the calculation.
2) If I were going to compare the two fields in a query, I’d add a result field to the table source of the query and call it “ValueResult”. I'd select the two value fields (from a table or from tables and/or queries) as well as the third (result) field. In the field row of the query I’d enter:
Code:
ValueResult: IIf([ValueOne]=[ValueTwo],0,1)3) If I were going to calculate a value on a form I'd want a control for both fields on the form. I would do this with a query selecting both fields (from the same or different tables and/or queries). The query would be the data source for the form. The controls would be bound to the two fields in the query. I would put an unbound text control on the form called “txtResult”. Then in the after update event of both of the value controls:
Code:
If Me.txtValueOne = Me.txtValueTwo Then
Me.txtResult = 0
Else
Me.txtResult = 1
End If- Thread starter
- #5
I created a query based on a table and a query to get both of my fields into the same source. I then created a from based on this query so I can do the calculation in the after update event. The only trouble is I can not update any data on the form. Do I need to alter a relationship or can this not be used for data entry?
- Thread starter
- #6
- Status
Similar threads
