How to share a calculation with several reports?

[Guest]

My Access database has one complex calculation which is needed in most
reports.

The calculation requires IF/THEN logic as the calculation uses various
fields from a row in the table:

IF columnB = columnY and columnC = columnY THEN (columnZ * (columnD +
columnE))

ELSE

IF columnC = columnY THEN (columnZ * (columnE))

ELSE (columnZ * columnD)

ENDIF

I think that it should be in a procedure but, being a "beginner", I can not
understand the steps to create a procedure and make a connection with each of
the reports.

I have two reference books but one is too basic and does not provide any
information and the other is way too complex!

Thanks for your help!

 

[tw]

you could put it into a function in a standard module

Select modules on the database window, then select new.

it will probably already have the line

Option Compare Database

add to that the lines

Option Explicit

function myComplexFunction(colB as variant, colC as Variant, colD as
Variant, colE as Variant, colY as variant, colZ as Variant)
'you can use the actual data type above if you prefer.

'put your "Complex" code here
IF columnB = columnY and columnC = columnY THEN
myComplexFunction = (columnZ * (columnD + columnE))
ELSE
IF columnC = columnY THEN
myComplexFunction = (columnZ * (columnE))
ELSE
myComplexFunction = (columnZ * columnD)
END IF
END IF
end function

call the function from your report control in the data source
=myComplexFunction(columnB, columnC, etc...)

 

[aaron.kempf]

this is the most beautiful functionality in the world; and I can't wait
for Microsoft to do this on the server side with SQL 2005.

-aaron

 

[Guest]

Thanks for the information but it looks like I need more...

The reports use a query to obtain the recordset. Each detail line in the
report matches a row in the recordset. Individual fields from each row are
used to perform the calculation for that detail line. The "IF/ELSE" logic is
used to determine which fields from that row are used for the calculation for
that row.

Should this query also be included in the module?

I created a function however I do not see how there is a connection between
the fields from the row of the recordset and the variables used in the
calculation in the function.

Example:

Tables:

Staff Table: ID
LastName
FirstName


Order Table: Order ID - order number
SaleDate - Date of order
SalePrice - Total price of order
SaleID - ID of staff person who sold
the order
PackID - ID of staff person who packed
the order
SaleCom - Commission rate for Sales person
PackCom - Commission rate for Packer
(person)

Query:

The query matches all the orders for each staff person, whether they were
the Sales person, the packer or both and the recordset is ordered by ID.

Calculation:

The calculation checks the ID with the SaleID and the PackID and then
computes the income for that person for that order:

IF (Staff!ID = Order!SaleID and Staff!ID = Order!PackID) THEN

Income = (Order!SalePrice * (Order!SaleCom + Order!PackCom)

ELSE

IF (Staff!ID = Order!SaleID) THEN

Income = (Order!SalePrice * (Order!SaleCom))

ELSE

IF (Staff!ID = Order!PackID THEN

Income = (Order!SalePrice * (Order!PackCom))

ELSE

Income = 0

END IF

END IF

END IF


The report then shows these details (individual transactions) and sums up
the total earnings.

Thanks very much for any assistance!

 

[tw]

You can call the function from the query. In the field name put the
following

myFieldData:myComplexFunction(columnB, columnC, etc...)

See if that works.