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!!!!!!!!!

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
http://I.Am/DougSteele
(no private e-mails, please)


"Matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
> 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
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Matt" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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!