Global Formula (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 15:24
Joined
Mar 14, 2017
Messages
2,658
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.
You don't need to drag all of those fields down to the design grid of your query, no. (I.E. if you want your query to return one ONE field - that function result and no other columns - you can).
You DO need to fully "supply" all parameters that your VBA function now requires, anytime it's called.

If I have a query with, potentially, columns: [col1] and [col2]

and I write a function like this:
Code:
Function ReturnSomething(Param1 as double, Param2 as double) as Something
....
End Function

That means you can type this in a blank 'column' grid in the query design:

ColumnAliasNameYouWant: ReturnSomething([col1],[col2])

Any time you run that function (including it in the query results is running it), you will be required to supply parameter VALUES for each parameter position. And as dbGuy mentioned, you will run into additional problems or unexpected results if you wrote a function that either assumes or requires all passed-in parameters to be of type 'double' (or implicitly convertable as such), and, one of the records in your query result ends up being for example, the word "spaghetti", or a Null.
The solution to that? Up to you...Either handle it in your query, or in your function. An example of handling it in your query might be an IIF statement that only calls the function if the columns are all Double. Or maybe handles Null with NZ. The possibilities are numerous, as you can see.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:24
Joined
Feb 19, 2013
Messages
12,571
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.
categories is just a step on the way. If you add another function to your module you'll see 2 'values'
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.
yes you do - they may be defined in the function (as doubles) but not values - the function only works on the parameters you have provided. You could redesign your function to lookup all the values but would still need a pointer like an ID to find the right record - and it would be considerably slower
 

Tskutnik

Registered User.
Local time
Today, 18:24
Joined
Sep 15, 2012
Messages
220
Thanks so much everyone - really helpful.
I think I am good, but there is a second part to this that I'll reach out about. It may take a coder some time to get through - maybe a day here and there - so do any of you do any side (paid) work? I'd need some direct coding (to spec) and some time for Q&A about various topics. I want to learn more about how this all works. You can contact me offline at tskutnik@theenosysgroup.com.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:24
Joined
Oct 29, 2018
Messages
13,131
Thanks so much everyone - really helpful.
I think I am good, but there is a second part to this that I'll reach out about. It may take a coder some time to get through - maybe a day here and there - so do any of you do any side (paid) work? I'd need some direct coding (to spec) and some time for Q&A about various topics. I want to learn more about how this all works. You can contact me offline at tskutnik@theenosysgroup.com.
Hi. Glad to hear you're good now. Good luck with your project.
 

Users who are viewing this thread

Top Bottom