Need Bound field in report to use that data? 2

[mkasson]

I have functions in a report module and Access says it is not finding certain fields. The functions return #error without error trapping. The Err.Description is "Lien Database can't find the field 'OrigAmt' referred to in you expression." The report's control source includes all of the neccesary fields (SELECT Liens.*, * FROM Liens.

Below is one of the functions:

Public Function CalcBal(evalDate As Date) As Double
Dim Penalty As Double

On Error GoTo Err_CalcBal

If evalDate = -1 Then
evalDate = Date
End If

'Determine penalty per NJSA 54:5-61
Penalty = 0
Select Case Nz([OrigAmt])
Case Is > 10000
Penalty = 0.06
Case Is > 5000
Penalty = 0.04
Case Is > 200
Penalty = 0.02
End Select

' Includes recording, search and penalty;   NEEDS LEGAL
CalcBal = Nz([OrigAmt]) * (1 + Nz([OrigRate]) * (evalDate - Nz([OrigDate])) / 365) _
        + 29 + Nz([OrigAmt]) * Penalty _
        + Nz([S1Amt]) * (1 + 0.18 * (evalDate - Nz([S1Date])) / 365) _
        + Nz([S2Amt]) * (1 + 0.18 * (evalDate - Nz([S2Date])) / 365) _
        + Nz([S3Amt]) * (1 + 0.18 * (evalDate - Nz([S3Date])) / 365) _
        + Nz([S4Amt]) * (1 + 0.18 * (evalDate - Nz([S4Date])) / 365) _
        + Nz([S5Amt]) * (1 + 0.18 * (evalDate - Nz([S5Date])) / 365) _
        + Nz([S6Amt]) * (1 + 0.18 * (evalDate - Nz([S6Date])) / 365) _
        + Nz([S7Amt]) * (1 + 0.18 * (evalDate - Nz([S7Date])) / 365) _
        + Nz([S8Amt]) * (1 + 0.18 * (evalDate - Nz([S8Date])) / 365) _
        + Nz([S9Amt]) * (1 + 0.18 * (evalDate - Nz([S9Date])) / 365) _
        + Nz([S10Amt]) * (1 + 0.18 * (evalDate - Nz([S10Date])) / 365) _
        + Nz([S11Amt]) * (1 + 0.18 * (evalDate - Nz([S11Date])) / 365) _
        + Nz([S12Amt]) * (1 + 0.18 * (evalDate - Nz([S12Date])) / 365) _
        + Nz([S13Amt]) * (1 + 0.18 * (evalDate - Nz([S13Date])) / 365) _
        + Nz([S14Amt]) * (1 + 0.18 * (evalDate - Nz([S14Date])) / 365) _
        + Nz([S15Amt]) * (1 + 0.18 * (evalDate - Nz([S15Date])) / 365) _
        + Nz([S16Amt]) * (1 + 0.18 * (evalDate - Nz([S16Date])) / 365)
            
Exit_CalcBal:
    Exit Function

Err_CalcBal:
    MsgBox Err.Description
    Resume Exit_CalcBal

End Function

I saw in Thread 703-757058 an indication by Steve101 that reference can't be made to fields from the control source that are not included as a bound field on the report.

When I added bound fields for all the fields from the field selector, the functions work fine. I did notice that the bound fields had/have the same name as the db field if that points to anything.

Each record in my database has about 40 numbers and dates used for calculation and I would hate to have to keep putting them in every report, even if they are invisible.

Is there a better way?

Thanks.

- MSK

 

[SteveR77]

MSK,


The fields must be there for the calculation to work.

However, you can copy a report that has the 40+ fields as a new object and modify it so you do not have to place the 40+ fields over and over again. The advantage is you will know that the logic works from one report to another when you do the copy from one report to the next and hopefully it will save on the time it would take to debug.


HTH,

Steve

 

[mkasson]

Thanks again Steve.

- Mark

- MSK

 

[dhookom]

I would caution against hard-coding all these values into reports. IE" 0.18, 10000, 0.06, 5000, 0.04, 200, and 0.02.

These seem like values that might change in the future. I would either create a module outside the report to store this calculation or at least create global variables. It would be better to place these values in tables so that they could be modified without touching code or expressions.

Also, your repeating fields suggest un-normalized tables. I could be wrong.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mkasson]

Thanks Duane.

Actually all those numbers are determined by state statute and have not changed in decades.

However, I would like to put the calculations in a separate module. It seems that when I put the function in a separate code module, I cannot access the database fields the way I do here; they seem to be out of scope.

Is there a way to do this?

(Also, if you have a thought on a workaround for my original question, I'd greatly appreciate it.)

You sensed the un-normalized table correctly. I just haven't taken the time to code the calculations, the forms and the exporting to Excel the right way. However, this was quicker to implement and start working with. One day.

Thank you.

- MSK

 

[dhookom]

To place all the calculations in a separate module, you would have to send in all the field values etc. Since your table seems un-normalized, this would be quite a few arguments unless you create a recordset of the values and act on them in the module.

The only way of getting around placing all the controls in the report is to open a recordset to grab the values from a source other than your report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.