set field data as control source of another field in a report

[Hisham Al Masri]

In a table I have fields as [a], .... and so on.
for each record I have a special calculation, say
[a]+ for a recored;
[a]**[d] for another recored, and so on.
I would like to use these calculation formulas which I have saved in a field
of the same record as a calculation (as a data in a seperate field) in a
report

 

[Evi]

Have I misunderstood this. When you say that you have a special calculation
for each record in your table, do you mean that you have typed the text
a + b + c
in your table?
If yes, may I suggest that you do this in a query grid so that the query
shows the result of your calculation ie in Design View of the Query which
contains your table fields, type, for example:
MyPlus: [a] + + [c]
or in the next column
Multiplic:[a]**[c]
Click on Insert, Report, include these calculated fields they will be in
your report.
you can use group footers and report footers to eg sum up all the [a]'s

Alternatively you may actually mean that you have a field in your table into
which you have typed a calculation as text, to be turned into a real
calculation in a query or report. If yes (and I can't imagine why you would
want to do this), then it gets much more complex and it will depend on what
sort of calculation you have typed.
Give more details
Evi

 

[Allen Browne]

Okay, this requires 2 steps:
- a way to substitute the actual field values for the field names, and
- a way to evaluate the resultant expression.

The example below illustrates how you could use the Format event of the
report section where you want the result calculated. It makes several
assumptions:

- For each name in the expression, there is a control on the report with
that name. (This is important due to the way the report optimizer sometimes
doesn't bother to fetch fields if it doesn't see a control on the report
bound directly to the field.)

- Every field name is enclosed in square brackets, and none are of the form:
[Table1].[Field2]
If necessary, alias the field in a query to meet this requirement, or adapt
the code to suit.

- The report has an unbound text box to display the result, and set its
Format property so Access knows its intended data type.

- All the expressions are valid. (Error handling not shown.)

In this example, the field containg the calculation is named Calc, and the
unbound text box for displaying the result is named Text6. The
CalcExpression() function can go in a standard module, so you can call it
from different reports.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Text6 = CalcExpression(Me, Me.Calc.Value)
End Sub

Public Function CalcExpression(rpt As Report, varExpression As Variant) As
Variant
Dim varArray As Variant
Dim i As Long
Dim lngPos As Long
Dim lngLen As Long
Dim strName As String
Dim strOut As String

If Len(varExpression) > 0 Then
varArray = Split(varExpression, "[")
If IsArray(varArray) Then
For i = LBound(varArray) To UBound(varArray)
lngPos = InStr(varArray(i), "]")
If lngPos > 0 Then
strName = "[" & Left(varArray(i), lngPos)
strOut = strOut & rpt.Controls(strName)
End If
lngLen = Len(varArray(i))
If lngPos < lngLen Then
strOut = strOut & Mid(varArray(i), lngPos + 1)
End If
Next
End If
Debug.Print strOut, Eval(strOut)
End If
If strOut <> vbNullString Then
CalcExpression = Eval(strOut)
Else
CalcExpression = Null
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...