Global Formula (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
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
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
Hi Tony. Have you tried converting the formula into a function?
 

Tskutnik

Registered User.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
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
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
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]
which asks me for the Parameter value instead of giving back the answer.

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
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
It does. I'm still getting hung up somewhere.
The Module now has this:

PLEASE USE CODE TAGS

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
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
It does. I'm still getting hung up somewhere.
The Module now has this:

PLEASE USE CODE TAGS

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
Local time
Today, 16:50
Joined
Feb 19, 2013
Messages
16,553
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

your other queries

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

Tskutnik

Registered User.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
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.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
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
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
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
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
1601401382021.png


See the highlighted location
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:50
Joined
Sep 15, 2012
Messages
229
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

1601402436956.png
 

Attachments

  • 1601402328508.png
    1601402328508.png
    25.9 KB · Views: 83
  • 1601402356964.png
    1601402356964.png
    118.3 KB · Views: 89

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
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...
 

Users who are viewing this thread

Top Bottom