help with coding structure for calculations

  • Thread starter Thread starter Michael C via AccessMonster.com
  • Start date Start date
M

Michael C via AccessMonster.com

I have a report that needs to show a possible of 4 different calculated
results depending on given criteria:
There are 4 production lines that calculate their end-numbers differently
depending on the line in question.
I am looking at creating a module that I can call to an individual cell, that
will have some sort of case statement / elseif statement that will do the
calculations. However, I haven't really used modules cold turkey before,
and could use some ideas on the basic structure. I could probably write the
If statements, but how to set it up?

Perry
 
Michael,

A custom function is the way to go. This can either be declared as Public
in a global module, or within the report's module. The code below assumes
the line is identified by a LineNumber which is a field in the underlying
recordset of the report. I've assigned arbitrary values to it in the various
cases.

Function MyEndNumber() As Single
Select Case [LineNumber]
Case 102
MyEndNumber = .....place calculated expression here
Case 229
MyEndNumber = .....place calculated expression here
... other cases
Case Else ' the default
MyEndNumber = .....place calculated expression here
End Select
End Function

Then set the ControlSource of your report control to:

= MyEndNumber()


Hope that helps.
Sprinks
 
Back
Top