Change Control Calculation to Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have form 'frmBPData' and a subform 'sfrmBPPayments. The subform footer
sums subform field 'BPMonths' as 'Sum([BPMonths]): name 'BPTotalMonths'.
Query 'qryBPData' is the record source for 'frmBPData'. Control
'BPIssueDate' is a field in tblBPData and is used in 'qryBPData' and is a
data entry field on 'frmBPData'.

There is a calculated control on frmBPdata 'named 'BPExpireDate' that is
calculated by the formula
'IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate])," ")

The problem is that I need to use 'BPExpireDate' on several reports. How
can I convert the calculated value of 'BPExpireDate' on 'frmBPData' to a
calculated field on 'qryBPData' so I can use it on reports?
 
Dear Paddler:

I'm guessing that you want the value selected on your form to be available
after the form has closed. I recommend placing the value in a public
variable. Write a public function that returns the value of this variable.
Use that function in any query you like. The value will persist until it
has been set.

You may need to consider what to do if the value has not yet been set. Some
user opens the database and goes to print some report that requires this
value, but it has not yet been set. By having some initial value for the
variable, or by having it be a variant which is initially null, you can have
a way of testing whether it has been set. Or, you can have a table that
stores the value of this variable, possibly with distinct values for each
user. Then, the function could look it up for you, using the current user's
identification. The identification can be by computer (using the network
computer name as key) or by a constrained user login name.

There is much to decide, and each choice will behave differently. An
examination of your requirements may be the first step toward choosing a
solution.

Tom Ellison
 
Tom Ellison said:
Dear Paddler:

I'm guessing that you want the value selected on your form to be available
after the form has closed. I recommend placing the value in a public
variable. Write a public function that returns the value of this variable.
Use that function in any query you like. The value will persist until it
has been set.

You may need to consider what to do if the value has not yet been set. Some
user opens the database and goes to print some report that requires this
value, but it has not yet been set. By having some initial value for the
variable, or by having it be a variant which is initially null, you can have
a way of testing whether it has been set. Or, you can have a table that
stores the value of this variable, possibly with distinct values for each
user. Then, the function could look it up for you, using the current user's
identification. The identification can be by computer (using the network
computer name as key) or by a constrained user login name.

There is much to decide, and each choice will behave differently. An
examination of your requirements may be the first step toward choosing a
solution.

Tom Ellison


Paddler said:
I have form 'frmBPData' and a subform 'sfrmBPPayments. The subform footer
sums subform field 'BPMonths' as 'Sum([BPMonths]): name 'BPTotalMonths'.
Query 'qryBPData' is the record source for 'frmBPData'. Control
'BPIssueDate' is a field in tblBPData and is used in 'qryBPData' and is a
data entry field on 'frmBPData'.

There is a calculated control on frmBPdata 'named 'BPExpireDate' that is
calculated by the formula
'IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate]),"
")

The problem is that I need to use 'BPExpireDate' on several reports. How
can I convert the calculated value of 'BPExpireDate' on 'frmBPData' to a
calculated field on 'qryBPData' so I can use it on reports?
Click to expand...
Click to expand...
Tom

Yes I need to have the value available after the form is closed. The real
solution would be to enter the data into tblBPData as field BPExpires. Yes I
have read all the warnings about putting calculated values in a table but I
think this would be a reasonable exception. People can buy more months and
they are entered in the subform. When control 'BPExpireDate is recalculated
for more months, I could use the update event and set value command to copy a
new value to [tblBPData]![BPExpires].

Some background: I have 4000 BP's (Building Permits) with about 200 still
active and eligible to buy more months. The remaining 3800 BP's are closed
and we have no interest in their Expire Dates. There are several BP tracking
reports that need to know if open projects have expired BP's.

Using a Public Function might work if I could write the function. I have
tried writing a Public Function but so far have only #Error & #Name responses
to my efforts. The only formula that I have written that works is the one
listed in my original post. The problem is using the correct nomenclature
for the various fields in tables, form, subform and calculated BPTotalMonths.

Paddler
 
Back
Top