Public function

[Tskutnik]

All, Thanks for the help. Here is an easy one for those of you that know what they are doing....

I'll have a formula that will be used in 10 queries. I'd like to setup one Public formula and reference it 10 times instead of having 10 separate full formulas in the 10 different queries. I looked this up and cannot quite find the answer.

Here is some detail:
Source tables.fields with the data are:
Assignment.BillRate
Multipliers.Period
(trust me there will be a relationship between the two)

The formula would be: BillValue = Assignment.BillRate * Multipliers.Period

So in queries the BillValue would be included in formulas, like this:
EXPR1: BillValue * 12

Question1: Is this the public function format?

Public Function BillValue() As Currency
BillValue = [assignments].[Billrate] * [Multipliers].[period]
End Function


Question 2: Can I call this in a formula, and is there a required format?

 

[CJ_London]

you need to include the parameters and refer to them instead

Public Function BillValue(BillRate as variant, Period as variant) As variant
BillValue = Billrate * period
End Function

use variant to allow the function to accept and pass nulls

and in your query you would have

ValueofBill:BillValue([assignments].[Billrate],[Multipliers].[period])

 

[plog]

I'll have a formula that will be used in 10 queries. I'd like to setup one Public formula and reference it 10 times instead of having 10 separate full formulas in the 10 different queries.

Why 10 queries? Can you make it so that you have 1 base query where you use the function and then base the other 10 queries off that base query?

For example, let's say you had 1 query per month where you used that calculation. Each query would have that calculation and then criteria on the month to limit the records returned to the specific month.

Instead you build your base query, do the formula there and then build your 12 monthly queries on the base query and apply your month criteria. That way you use the formula 1 time, but it flows into the 12 queries you need.

 

[Tskutnik]

Plog - I'm being a little dramatic for effect. You are completely correct that a single referenceable query could solve the problem, but I'm trying a different approach in part to learn how to use Public functions and in part based on how the rest of the DB is setup.