Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KnooKie
Aged Yak Warrior
623 Posts |
 Posted - 2003-08-07 : 07:52:54
|
| Access97.....i have a subform in which is bound to a query.One of the fields in the query needs to hold a calculation of two of the other fieldse.g.Field3 = Field1 / Field2How can i perform this calculation for field3 and keep field3 bound to a field in the underlying query ? Can this be done in a query ? I'll need to requery the subform every time a value in field1 or field2 is changed to display the new calculation i guess.This seems like it should be really simple....perhaps i am being thickthanks====Paul |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-07 : 12:46:26
|
| It doesn't help to bind a control to that calculated field, you won't be able to edit it on the form anyway.Keep the query the same, but create an unbound control and put the calculation as it's source. That way it will recalculate when either of the other columns are updated (and you won't have to requery to show it) |
 |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2003-08-07 : 12:54:52
|
| thanks robThe table the underlying query has in it has a field for storing the calculated result in. I was wondering whether there was an easy way of putting the calculated value into this field via the underlying query. I know i can code it in but just thought there may be an easier way. It currently is exactly how you say in your post but it doesn't then save the result in the table (because the source is a calculation and not the field where the result needs to be stored) although as you say happily displays ok.I guess i'll code it in then.I've been asked to sort this little bit out for someone ion one of their databases so i can't make any major changes anywhere.thanks again====Paul |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-07 : 13:08:16
|
| In that case, you would have to bind the control, and then set its default value to equal the calculation. You'd have to write some VB in order to update that control whenever the other two controls change value. It won't work by using the underlying query (I don't think it will anyway) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-07 : 13:50:13
|
| I usually just put code in the AfterUpdate events in all of the text boxes involved in the calculation.By the way -- instead of doing events the "VB way" where each event has it's own function, Access is great because you can create 1 function and have multiple events on multiple object call it.i.e.,function UpdatePercentage() txtPct = txtNumerator / txtDenominatorend functionthen in "AfterUpdate" event (in the properties box) of txtNumerator and txtDenominator, just type:=UpdatePercentage()much eaiser than having 2 functions, 1 for each text box, as they change.- Jeff |
|
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2003-08-07 : 16:53:35
|
| Excellent thanks guysgood idea Jeff i will do that too====Paul |
|
|
|
|
|
|
Calculation in query.....