Calling a function from a query that returns an array

[Matt]

Hi All,

I have a function that returns an array of doubles. I want to call
this function from a query and return the index's across 3 different
query fields. Something like:

SELECT getGainUsed([Import Date],[Owner SSN],[Contract Number])(0),
getGainUsed([Import Date],[Owner SSN],[Contract
Number])(1),getGainUsed([Import Date],[Owner SSN],[Contract Number])(2)
FROM .....

I know that this would work if the getGainUsed function returned simply
a double (with the index removed) but I can not to seem to get it to
work for an array.

For testing purposes, I have created this simple function:
..
..
..

Public Function getGainUsed(importDate As Date, ssn As String, contract
As String)
Dim arrValues(2) As Double

arrValues(0) = 0.1
arrValues(1) = 1.1
arrValues(2) = 2.1

getGainUsed = arrValues
End Function
..
..
..


When I try to reference an index of this array, as I do in the above
SQL statement, I get #Error's


What am I doing wrong?




Any help is appreciated!!!!!!!!!

 

[Douglas J. Steele]

I think you may have to create 3 "wrapper functions", each one of which
returns one of the values.

Public Function getGainUsed0( _
importDate As Date, _
ssn As String, _
contract As String _
) As String

getGainUsed0 = getGainUsed(importDate, ssn, contract)(0)

End Function

etc and use

SELECT getGainUsed0([Import Date],[Owner SSN],[Contract Number]),
getGainUsed1([Import Date],[Owner SSN],[Contract Number]),
getGainUsed2([Import Date],[Owner SSN],[Contract Number])
FROM .....

or maybe

Public Function getGainUsedSub( _
importDate As Date, _
ssn As String, _
contract As String, _
SubstringValue As Integer _
) As String

getGainUsedSub = getGainUsed(importDate, ssn, contract)(SubstringValue)

End Function

and then use

SELECT getGainUsedSub([Import Date],[Owner SSN],[Contract Number], 0),
getGainUsedSub([Import Date],[Owner SSN],[Contract Number], 1),
getGainUsedSub([Import Date],[Owner SSN],[Contract Number], 2)
FROM .....

 

[Matt]

I think you may have to create 3 "wrapper functions", each one of which
returns one of the values.

Public Function getGainUsed0( _
importDate As Date, _
ssn As String, _
contract As String _
) As String

getGainUsed0 = getGainUsed(importDate, ssn, contract)(0)

End Function

etc and use

SELECT getGainUsed0([Import Date],[Owner SSN],[Contract Number]),
getGainUsed1([Import Date],[Owner SSN],[Contract Number]),
getGainUsed2([Import Date],[Owner SSN],[Contract Number])
FROM .....

or maybe

Public Function getGainUsedSub( _
importDate As Date, _
ssn As String, _
contract As String, _
SubstringValue As Integer _
) As String

getGainUsedSub = getGainUsed(importDate, ssn, contract)(SubstringValue)

End Function

and then use

SELECT getGainUsedSub([Import Date],[Owner SSN],[Contract Number], 0),
getGainUsedSub([Import Date],[Owner SSN],[Contract Number], 1),
getGainUsedSub([Import Date],[Owner SSN],[Contract Number], 2)
FROM .....



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi All,

I have a function that returns an array of doubles. I want to call
this function from a query and return the index's across 3 different
query fields. Something like:

SELECT getGainUsed([Import Date],[Owner SSN],[Contract Number])(0),
getGainUsed([Import Date],[Owner SSN],[Contract
Number])(1),getGainUsed([Import Date],[Owner SSN],[Contract Number])(2)
FROM .....

I know that this would work if the getGainUsed function returned simply
a double (with the index removed) but I can not to seem to get it to
work for an array.

For testing purposes, I have created this simple function:
.
.
.

Public Function getGainUsed(importDate As Date, ssn As String, contract
As String)
Dim arrValues(2) As Double

arrValues(0) = 0.1
arrValues(1) = 1.1
arrValues(2) = 2.1

getGainUsed = arrValues
End Function
.
.
.


When I try to reference an index of this array, as I do in the above
SQL statement, I get #Error's


What am I doing wrong?




Any help is appreciated!!!!!!!!!

Thanks Doug, I ended up just retuning a ; delimited string (since it
was only 3 index's) and split it apart in the query ... this way I only
had to run the code once, which is what I was going for (I realized
after I posted that what I was trying to do would have run the code 3
times for each row as opposed to once)


Thanks for your quick response tho ... I know the board has got me out
of many jams!