How do I use a calculated field in another field's Control Source?

  • Thread starter Thread starter upsman via AccessMonster.com
  • Start date Start date
U

upsman via AccessMonster.com

I am using a function to calculate the number of days in a quarter. I have
the Control Source for field "A" set to
=CalcQtr1Days([AdminDate]). I am calling CalcQtr1Days and passing the
AdminDate. It calculates the number of days I'm looking for and returns that
number in my field. Then, I want to use that number, along with DaysAbsent,
to calculate DaysPresent. So, in field "B" Control Source, I have
=CalcQtr1Days - DaysAbsent.
This gives me #Name? in field "B". I've also tried =DaysPresent =
CalcQtr1Days - DaysAbsent but doesn't work either. What is the syntax for
using the value calculated in field "A" in my calculation for field "B"?

Thanks,
Rod
 
Right-click the first text box and choose Properties.
On the Other tab of the Properties box, what is the Name of this text box?
For example, it might be named "Text12"

If so the next text box needs a Control Source of:
=[Text12] - [DaysAbsent]
You also need to check the Name of this text box, and make sure it is not
the same as a field. (For example, it cannot be named DaysAbsent.)

If Access is still confused, try setting the Format property of these text
boxes to:
General Number
For an explanation see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
Thank you Allen, that worked. Now my next question - if DaysAbsent is 0 then
I want it to show the value in "Text12". Right now the next text box shows a
blank. I would have thought, (having a value of 42 in Text12) that 42 - 0 =
42. But [Text12] - [DaysAbsent] returns a blank when DaysAbsent is 0. Any
suggestions?


Allen said:
Right-click the first text box and choose Properties.
On the Other tab of the Properties box, what is the Name of this text box?
For example, it might be named "Text12"

If so the next text box needs a Control Source of:
=[Text12] - [DaysAbsent]
You also need to check the Name of this text box, and make sure it is not
the same as a field. (For example, it cannot be named DaysAbsent.)

If Access is still confused, try setting the Format property of these text
boxes to:
General Number
For an explanation see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
I am using a function to calculate the number of days in a quarter. I have
the Control Source for field "A" set to
Click to expand...
[quoted text clipped - 9 lines]
for
using the value calculated in field "A" in my calculation for field "B"?
Click to expand...
Click to expand...
 
Okay, I think I answered my own question. I set the Control Source to be =
[Text12] - (NZ( [DaysAbsent])) and the fields are displaying the values I
want.
Thank you Allen, that worked. Now my next question - if DaysAbsent is 0 then
I want it to show the value in "Text12". Right now the next text box shows a
blank. I would have thought, (having a value of 42 in Text12) that 42 - 0 =
42. But [Text12] - [DaysAbsent] returns a blank when DaysAbsent is 0. Any
suggestions?
Right-click the first text box and choose Properties.
On the Other tab of the Properties box, what is the Name of this text box?
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
Back
Top