How do I pass arguments to a user defined function from a querydef?

[Marty L]

I want to be able to select the max from a set of columns. I don't want to
use IIf.

I have written a user defined function to find the maximum from a list of
values:
----------------------------------------------------------------------------
------
Public Function MaxInArray(varArray As Variant) As Variant
Dim varItem As Variant
Dim varMax As Variant
Dim intI As Integer

If IsArray(varArray) Then
If UBound(varArray) = -1 Then
MaxInArray = Null
Else
varMax = varArray(UBound(varArray))
For intI = LBound(varArray) To UBound(varArray)
varItem = varArray(intI)
If varItem > varMax Then
varMax = varItem
End If
Next intI
MaxInArray = varMax
End If
Else
MaxInArray = Null
End If
End Function
----------------------------------------------------------------------------
-----------

this works if I call it from a sub routine:
x = MaxInArray(array(1,2))
for example, but I want to be able to call it from a querydef:
where I have a field defined as the following expression:

Expr1: MaxInArray(Array([table1.field1],[table1.field2]))

When I try this I geta popup "Data type mismatch in criteria expression"
I know this is happening prior to the function being called because I have a
breakpoint in the function

Basic question - how do you build a function that you can use in a querydef
with multiple (1,2 or more) arguments? How do you pass the arguments?

Thanks, Marty

 

[Jeff Boyce]

Marty

If your table has "repeating columns" (i.e., keeping the same kind of data
in multiple columns, like Weight1, Weight2, Weight3, ... WeightN), you will
have headaches, mostly from banging your head against the keyboard trying to
do things like this.

My first suggestion, if this is your situation, would be to read up on
"normalization", and to modify your table structure to handle the
"one-to-many" data relationship. After that, finding the Max() of a list of
rows, in a single column, is a simple Totals query.

Good luck!

Jeff Boyce
<Access MVP>

 

[Marty L]

That is exactly the problem - I'm working with a dataset that I've imported
from FileMaker Pro (and the procedure will be for users to periodically do
this) that is not normalized and I'm trying to create a query to consolidate
multiple columns into a single column for this table. I've gotten this to
work with nested IIf statements, but thought it would be cleaner defining a
function - plus I think this would be a useful thing to know how to do. By
the way - the function does get called if I call it with one argument - I
just need to figure out how to call it with multiple, but unspecified,
number of arguments. Thanks for your reply, Marty

 

[John Spencer (MVP)]

Change your declaration to use ParamArray and then call the function

Public Function MaxInArray(ParamArray varArray() As Variant) As Variant

Expr1: MaxInArray([table1.field1],[table1.field2])

 

[Jeff Boyce]

Thanks John! (I hadn't had the occasion to use that before).

Jeff Boyce
<Access MVP>

 

[Marty L]

Thanks guys - exactly what I'm looking for! Don't know how I missed that
one searching my half dozen books and manuals! Marty