Custom Function to return array

Hi everyone,

I'm hoping that what I'm trying to achieve is possible and I am not
sure that it can and couldn't find the answer.

I originally wrote a custom function to perform a DLOOKUP and return
the results which is used in a query and then started writing another
function to do almost the same DLOOKUP job but rather than returning
the "cost code" it would return the "cost code description". Thus,
why I thought having a function that would return both values as an
array would be even better but not so sure.

The original function was:

-------
Public Function LookupSun(Costcode As String) As Variant

Dim LookupSunY, LookupSunZ, VarX, VarY, VarZ As Variant
VarX = Mid(Costcode, 4, 5)
VarY = DLookup("[COST_CENTRE_SUN]", "SUNCODES", "[COST_CODE_SUN_CODE]
= '" & VarX & "'")
VarZ = DLookup("[COST_CENTRE_SUN_DESCRIPTION]", "SUNCODES",
"[COST_CODE_SUN_CODE] = '" & VarX & "'")

If Not IsNull(VarY) Then
LookupSunY = VarY
Else
LookupSunY = "ERROR - No Code Found"
End If
If Not IsNull(VarZ) Then
LookupSunZ = VarZ
Else
LookupSunZ = "ERROR - No Code Found"
End If

LookupSun = Array(LookupSunY, LookupSunZ)

End Function
-------

The problem is that I'm sure that the DLOOKUP functions work but then
I realised that the problem is that as I have to tell the function
what data to use, how would I go about telling the query which value
to use.

EG: Expr1:=LookupSun([Costcode],(1) as the array functions are in the
format of =LookupSun(1) or =LookupSun(2)

I thought it would be much easier to have the 1 function do the
calculations and I can refer to it in the query.

If you really want to use these return values in a query (and if so, why are
you trying to use the ":=" operator, which is for specifying values for
named arguments in a procedure call?) then you would be much better off
joining to the table "SUNCODES" in the query.

Something like this:

SELECT Nz(COST_CENTRE_SUN,"ERROR - No Code Found"),
Nz(COST_CENTRE_SUN_DESCRIPTION,"ERROR - No Code Found") FROM sometable LEFT
JOIN SUNCODES ON sometable.Costcode = Mid(SUNCODES.COST_CODE_SUN_CODE, 4, 5)

n.b. a query such as this will need to be maintained in the SQL view because
the query designer is too stupid to handle joins which involve functions.

However, if this is something you intend to do regularly (i.e. it's not some
kind of data maintenance one-off), then you would do far better to store the
foreign key in the correct format on SUNCODES so that you don't need to use
the Mid function in the join.

Incidentally, some comments on your code:

Why is VarX a variant? It could just as easily (and more efficiently) be a
string. As could LookupSunY and LookupSunZ.

Why carry out two successive Dlookups on the same record? You could get
both values with just one database access if you opened a forward-only
recordset containing the record e.g.

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT COST_CENTRE_SUN,
COST_CENTRE_SUN_DESCRIPTION FROM SUNCODES WHERE COST_CODE_SUN_CODE = '" &
VarX & "'", dbOpenForwardOnly)

BTW, I don't know the answer to your original question, I doubt that it's
possible.


"Forgone" <stevenn@forgone.org> wrote in message
news:4c08374b-0506-4339-b758-49bf2f108b2e@n38g2000prl.googlegroups.com...
> Hi everyone,
>
> I'm hoping that what I'm trying to achieve is possible and I am not
> sure that it can and couldn't find the answer.
>
> I originally wrote a custom function to perform a DLOOKUP and return
> the results which is used in a query and then started writing another
> function to do almost the same DLOOKUP job but rather than returning
> the "cost code" it would return the "cost code description". Thus,
> why I thought having a function that would return both values as an
> array would be even better but not so sure.
>
> The original function was:
>
> -------
> Public Function LookupSun(Costcode As String) As Variant
>
> Dim LookupSunY, LookupSunZ, VarX, VarY, VarZ As Variant
> VarX = Mid(Costcode, 4, 5)
> VarY = DLookup("[COST_CENTRE_SUN]", "SUNCODES", "[COST_CODE_SUN_CODE]
> = '" & VarX & "'")
> VarZ = DLookup("[COST_CENTRE_SUN_DESCRIPTION]", "SUNCODES",
> "[COST_CODE_SUN_CODE] = '" & VarX & "'")
>
> If Not IsNull(VarY) Then
> LookupSunY = VarY
> Else
> LookupSunY = "ERROR - No Code Found"
> End If
> If Not IsNull(VarZ) Then
> LookupSunZ = VarZ
> Else
> LookupSunZ = "ERROR - No Code Found"
> End If
>
> LookupSun = Array(LookupSunY, LookupSunZ)
>
> End Function
> -------
>
> The problem is that I'm sure that the DLOOKUP functions work but then
> I realised that the problem is that as I have to tell the function
> what data to use, how would I go about telling the query which value
> to use.
>
> EG: Expr1:=LookupSun([Costcode],(1) as the array functions are in the
> format of =LookupSun(1) or =LookupSun(2)
>
> I thought it would be much easier to have the 1 function do the
> calculations and I can refer to it in the query.

On Sep 8, 4:39*pm, "bcap" <b...@nospam.nowhere> wrote:
> (why are you trying to use the ":=" operator) - Typing error
> joining to the table "SUNCODES" in the query. - the data doesn't match.

We're running from a "shared services" and the data provided comes in
any format.
In the table that I'm using the format of the data is in the format of
"920MM211NA" and what I need to do is convert the value "MM211" to the
cost centre which is "2340310" and then the description of "XYZ 123"

However, if this is something you intend to do regularly (i.e. it's
not some kind of data maintenance one-off), then you would do far
better to store the foreign key in the correct format on SUNCODES so
that you don't need to use the Mid function in the join. - I do
actually, the table SUNCODES has the code "MM211" set as the primary
key but the data supplied such as the Employee FTE data needs to be
modified to obtain that code.

Why is VarX a variant? It could just as easily (and more efficiently)
be a string. As could LookupSunY and LookupSunZ. - I originally had
VarX as a string LookupSunY & Z had to be variants because it kept
getting its knickers in a not about "null" values. A variant can be
null whilst a string couldn't. What I found is that there are a
number of codes missing in the "SUNCODES" table. The NZ() would
resolve that.

Why carry out two successive Dlookups on the same record? You could
get both values with just one database access if you opened a forward-
only recordset containing the record e.g. - Still learning..... no
formal training, thus you could say i'm in the advanced stages of
newbie.

Thanks for the example, I'll give it a go.

> Something like this:
>
> SELECT Nz(COST_CENTRE_SUN,"ERROR - No Code Found"),
> Nz(COST_CENTRE_SUN_DESCRIPTION,"ERROR - No Code Found") FROM sometable LEFT
> JOIN SUNCODES ON sometable.Costcode = Mid(SUNCODES.COST_CODE_SUN_CODE, 4, 5)
>
> n.b. a query such as this will need to be maintained in the SQL view because
> the query designer is too stupid to handle joins which involve functions.
>
> However, if this is something you intend to do regularly (i.e. it's not some
> kind of data maintenance one-off), then you would do far better to store the
> foreign key in the correct format on SUNCODES so that you don't need to use
> the Mid function in the join.
>
> Incidentally, some comments on your code:
>

>
> Why carry out two successive Dlookups on the same record? *You could get
> both values with just one database access if you opened a forward-only
> recordset containing the record e.g.
>
> Dim rs As DAO.Recordset
>
> Set rs = CurrentDb.OpenRecordset("SELECT COST_CENTRE_SUN,
> COST_CENTRE_SUN_DESCRIPTION FROM SUNCODES WHERE COST_CODE_SUN_CODE = '"&
> VarX & "'", dbOpenForwardOnly)
>
> BTW, I don't know the answer to your original question, I doubt that it's
> possible.
>
> "Forgone" <stev...@forgone.org> wrote in message
>
> news:4c08374b-0506-4339-b758-49bf2f108b2e@n38g2000prl.googlegroups.com...
>
>
>
> > Hi everyone,
>
> > I'm hoping that what I'm trying to achieve is possible and I am not
> > sure that it can and couldn't find the answer.
>
> > I originally wrote a custom function to perform a DLOOKUP and return
> > the results which is used in a query and then started writing another
> > function to do almost the same DLOOKUP job but rather than returning
> > the "cost code" it would return the "cost code description". *Thus,
> > why I thought having a function that would return both values as an
> > array would be even better but not so sure.
>
> > The original function was:
>
> > -------
> > Public Function LookupSun(Costcode As String) As Variant
>
> > Dim LookupSunY, LookupSunZ, VarX, VarY, VarZ As Variant
> > VarX = Mid(Costcode, 4, 5)
> > VarY = DLookup("[COST_CENTRE_SUN]", "SUNCODES", "[COST_CODE_SUN_CODE]
> > = '" & VarX & "'")
> > VarZ = DLookup("[COST_CENTRE_SUN_DESCRIPTION]", "SUNCODES",
> > "[COST_CODE_SUN_CODE] = '" & VarX & "'")
>
> > If Not IsNull(VarY) Then
> > * *LookupSunY = VarY
> > Else
> > * *LookupSunY = "ERROR - No Code Found"
> > End If
> > If Not IsNull(VarZ) Then
> > * *LookupSunZ = VarZ
> > Else
> > * *LookupSunZ = "ERROR - No Code Found"
> > End If
>
> > LookupSun = Array(LookupSunY, LookupSunZ)
>
> > End Function
> > -------
>
> > The problem is that I'm sure that the DLOOKUP functions work but then
> > I realised that the problem is that as I have to tell the function
> > what data to use, how would I go about telling the query which value
> > to use.
>
> > EG: Expr1:=LookupSun([Costcode],(1) as the array functions are in the
> > format of =LookupSun(1) or =LookupSun(2)
>
> > I thought it would be much easier to have the 1 function do the
> > calculations and I can refer to it in the query.- Hide quoted text -
>
> - Show quoted text -