# Global Formula (1 Viewer)

#### Tskutnik

##### Registered User.
Hey All, Easy one for you.

I have one long formula that applies to about 15 different queries. I'd rather give it a Name and then reference the name in each query instead of using the entire formula 15 different times.
The formula in each query always references the same field names, so it is always exactly the same.

I assume this is an easy one.

Since I'm not a coder, the more specific you are the netter.

Thanks guys... as always

Tony

#### theDBguy

##### I’m here to help
Staff member
Hi Tony. Have you tried converting the formula into a function?

#### Tskutnik

##### Registered User.
Hi Tony. Have you tried converting the formula into a function?
That's sort of my question. What is the way to do it? If converting it to a function is the right answer, then no, have not tried that yet.

#### theDBguy

##### I’m here to help
Staff member
That's sort of my question. What is the way to do it? If converting it to a function is the right answer, then no, have not tried that yet.
Depending on your "formula", you can try to create a custom function that you can call in your query, and it will return the result of the calculation to that query.

#### Tskutnik

##### Registered User.
TY let me give that a try

Staff member

#### Tskutnik

##### Registered User.
Sorry to have to ask this - I'm sure you will shake your head but I'm going to struggle to find the answer.

The Function is this:
Function ReturnCalc_NetZero() As Double
ReturnCalc_NetZero = ([Pos_Market] - [Pos_Market_PriorDay] - [ReturnImpact_FlowNet]) / ([Pos_Market_PriorDay] + [ReturnImpact_FlowNetTiming])
End Function

To call the function, in the query I have - Return_Market: [ReturnCalc_NetZero]

All of the fields referenced in the Function [Pos_Market] etc... are available to the query

Do I have to set the fields [Pos_Market], etc. as variables in the function?

#### theDBguy

##### I’m here to help
Staff member
All of the fields referenced in the Function [Pos_Market] etc... are available to the query

Do I have to set the fields [Pos_Market], etc. as variables in the function?
Probably, yes! You did say you want to use this global formula for multiple queries, right? So, yes, you will have to somehow pass to the function the values you want to calculate from that particular query.

Try defining your function like this:
Code:
``````Public Function ReturnCalc_NetZero(Pos_Market As Double, Pos_Market_PriorDay As Double, _
ReturnImpact_FlowNet As Double, ReturnImpact_FlowNetTiming As Double) As Double``````
Also try removing all the square brackets in the formula. You would then call the function in your query, passing all the values/columns/fields you need for the formula. For example:
Code:
``Return_Market: ReturnCalc_NetZero([Pos_Market], [Pos_Market_PriorDay], [ReturnImpact_FlowNet], [ReturnImpact_FlowNetTiming])``
Again, you don't use the square brackets around the function name. Instead, you use the parens to enclose the arguments, which can be in square brackets.

Hope that helps...

#### Tskutnik

##### Registered User.
It does. I'm still getting hung up somewhere.
The Module now has this:

Code:
``````Option Compare Database
Public Function ReturnCalc_NetZero(Pos_Market As Double, Pos_Market_PriorDay As Double, _
ReturnImpact_FlowNet As Double, ReturnImpact_FlowNetTiming As Double) As Double
ReturnCalc_NetZero = ReturnCalc_NetZero([Pos_Market], [Pos_Market_PriorDay], [ReturnImpact_FlowNet], [ReturnImpact_FlowNetTiming])
End Function``````

Within the query I'm trying to call the function and In on of the fields I have Return_Market: [ReturnCalc_NetZero]. The query is adding the parenthesis, as if it does not recognize the Function exists.

I really appreciate your help. once i get the syntax I'll need this for a bunch of other functions[/code]

#### theDBguy

##### I’m here to help
Staff member
It does. I'm still getting hung up somewhere.
The Module now has this:

Code:
``````Option Compare Database
Public Function ReturnCalc_NetZero(Pos_Market As Double, Pos_Market_PriorDay As Double, _
ReturnImpact_FlowNet As Double, ReturnImpact_FlowNetTiming As Double) As Double
ReturnCalc_NetZero = ReturnCalc_NetZero([Pos_Market], [Pos_Market_PriorDay], [ReturnImpact_FlowNet], [ReturnImpact_FlowNetTiming])
End Function``````

Within the query I'm trying to call the function and In on of the fields I have Return_Market: [ReturnCalc_NetZero]. The query is adding the parenthesis, as if it does not recognize the Function exists.

I really appreciate your help. once i get the syntax I'll need this for a bunch of other functions[/code]
Hi. You may have to re-read what I posted above. I said to remove the square brackets from the formula in your function and to remove the square brackets in your query and use parenthesis instead. I also said, to call the function passing the arguments from your query.

#### CJ_London

##### Super Moderator
Staff member
would think it should be something like this

Return_Market: ReturnCalc_NetZero(Pos_Market, Pos_Market_PriorDay ReturnImpact_FlowNet,, ReturnImpact_FlowNetTiming)

if this is a formula you use elsewhere based on the same table, you could just create is as a separate query,with a uniqueID then link the query in to the other queries.

Just a guess as to your situation

qryReturnMarket
Code:
``````SELECT ID,  [Pos_Market] - [Pos_Market_PriorDay] - [ReturnImpact_FlowNet]) / ([Pos_Market_PriorDay] + [ReturnImpact_FlowNetTiming] AS Return_Market
FROM myTable``````

Code:
``````SELECT *
FROM myQuery INNER JOIN qryReturnMarket ON myQuery.ID=qryReturnMarket.ID``````

#### Tskutnik

##### Registered User.
thanks for your help guys. DBGuy - sorry I misread your note. I'm very dyslexic so I miss those things sometimes. Im still poking around with this.

#### Tskutnik

##### Registered User.
All - Lets try this one step at a time so I am sure to not bother you too much.

This is the Module - does this part look OK for a global function?

Code:
``````Option Compare Database

Public Function ReturnCalc_NetZero(Pos_Market As Double, Pos_Market_PriorDay As Double, _
ReturnImpact_FlowNet As Double, ReturnImpact_FlowNetTiming As Double) As Double

ReturnCalc_NetZero = (Pos_Market - Pos_Market_PriorDay - ReturnImpact_FlowNet) / (Pos_Market_PriorDay + ReturnImpact_FlowNetTiming)

End Function``````

PS - Hope I code tagged this correctly.

#### theDBguy

##### I’m here to help
Staff member
All - Lets try this one step at a time so I am sure to not bother you too much.

This is the Module - does this part look OK for a global function?

Code:
``````Option Compare Database

Public Function ReturnCalc_NetZero(Pos_Market As Double, Pos_Market_PriorDay As Double, _
ReturnImpact_FlowNet As Double, ReturnImpact_FlowNetTiming As Double) As Double

ReturnCalc_NetZero = (Pos_Market - Pos_Market_PriorDay - ReturnImpact_FlowNet) / (Pos_Market_PriorDay + ReturnImpact_FlowNetTiming)

End Function``````

PS - Hope I code tagged this correctly.
The only thing I would add is when I gave you the suggestion on how to modify your function, I made an assumption about the data type for each field in your table (or column in your query). So, if they are not Double data types, you might get an error message when you use that function.

Okay, so to test if that function works, try entering the following in the Immediate Window.
Code:
``?ReturnCalc_NetZero(10,5.5,.5,2.5)``

#### Tskutnik

##### Registered User.
The only thing I would add is when I gave you the suggestion on how to modify your function, I made an assumption about the data type for each field in your table (or column in your query). So, if they are not Double data types, you might get an error message when you use that function.

Okay, so to test if that function works, try entering the following in the Immediate Window.
Code:
``?ReturnCalc_NetZero(10,5.5,.5,2.5)``

Got Compile Error: unexpected variable or procedure, not module.
I changed all the Doubles to Variants and got the same thing.

#### theDBguy

##### I’m here to help
Staff member
Got Compile Error: unexpected variable or procedure, not module.
I changed all the Doubles to Variants and got the same thing.
Where did you put your new function? It has to be in a Standard Module, so you can call it from anywhere in your applications.

#### Tskutnik

##### Registered User.

See the highlighted location

#### theDBguy

##### I’m here to help
Staff member
View attachment 85376

See the highlighted location
Okay, that's a common mistake. You'll need to give your Module a different name than the function inside it. So, try renaming your module as modReturnCalc_NetZero or rename your function something like fReturnCalc_NetZero.

#### Tskutnik

##### Registered User.
Awesome! That module name gave me the right Immediate result. Great tip.

Now second step - to reference the function in the query.
I used the Builder to find the select the Expression Value as below. That entered the formula in the Query Field, which I had to clean up to get it to look as it does below. I think it is working. Yay for us!

So 2 questions
1) What is the difference between selecting from the Expression Categories and Expression Values? They look like the same formula. Maybe they are only for my case.
2) Do I need to have all the fields listed in the query? I thought I'd be able to just type in the Function name ReturnCalc_NetZero without those variables since they are defined in the function - but that does not seem to work.

I want to make sure and understand this so I can do this on my own.
Thanks again for the help

#### Attachments

• 1601402328508.png
25.9 KB · Views: 19
• 1601402356964.png
118.3 KB · Views: 17

#### theDBguy

##### I’m here to help
Staff member
Hi. I'm about to step out of the office; but if no one offers an explanation by the time I get back later or tomorrow, I will give you the 411. Okay? Later...

Replies
8
Views
95
Replies
8
Views
151
Replies
12
Views
199
Replies
6
Views
147
Replies
6
Views
176