Isaac
Lifelong Learner
- Local time
- Today, 08:30
- Joined
- Mar 14, 2017
- Messages
- 8,738
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).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 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.