multiple function return values

[baga]

Using VBA in Acesss 2000.

Is it possible for a function to return two values? I am looping through an ADO recordset that has an unparsed name field. I would like to call a function from within the loop that parses names into first and last. Can I call the function once and have the parsed first and last names returned?

I have tried to write the function to return an array with the first and last names but how would I captrue the names in variables in the calling procedure without calling the function twice?

 

[Zathras]

Nope. Create two functions: One to return the first name and one to return the last name.

 

[jrbarnett]

Hi,

A function can only return one value, but it could return a delimited string (eg firstname;lastname) separated with a known delimiter that is not likely to appear in the results.
You can then use InStr to locate the position of the delimiter, and the Mid$ function to extract each individual item.

I am not aware of a function returning an array, but that doesn't mean it can't happen.

John

 

[baga]

Thanks!

I won't waste anymore time on it and just break it into two functions.

JM

 

[TonyJollans]

Hi baga,

A Function can only return a single something but that something can be a structure or an array. Prior to 2000 you could only return an array as a Variant, but in 2000 you can do it explicitly.

In your calling routine have code including this:

Dim myNames() as String
Dim FirstName As String
Dim LastNAme as String

myNames = SplitName(CompleteName)
FirstName = myNames(1)
LastName = myNames(2)

And then have your function defined like this:

Function SplitName(CompleteName As String) As String()

Dim ReturnArray(1 To 2) as String
ReturnArray(1) = "FirstName"

' Extracted by whatever process you use

ReturnArray(2) = "LastName"

' Extracted by whatever process you use

SplitName = ReturnArray

End Function

BUT, if your names are simply two word strings like "Tony Jollans" then, in A2K, you can use the VBA Split function to do what you want without needing custom code at all ..

Dim myNames() as String
Dim FirstName As String
Dim LastNAme as String

myNames = Split(CompleteName)
FirstName = myNames(0)
LastName = myNames(1)

Enjoy,
Tony

 

[Zathras]

Of course, if this is all in code, you can use a procedure (a.k.a. Sub) and define two (or more) arguments to get both (all) values back at the same time. It's just that you can't do it with a Function.

One other thing I have used in the past when it needed to be a function (i.e. a user-defined-function a.k.a. UDF) that I wanted to use in a spreadsheet and the calculation was time-consuming such that I didn't want to do it twice. (For example, looking up latitude and longitude for a zip code.) In that case, I wrote one function with an extra argument to indicate which result I wanted, then stored the arguments and results in global variables. That way when the function is called again with the same arguments (not counting the extra one), I could quickly return the desired result without going thru the calculations again.

 

[baga]

Thanks all for you help. You guys really are great!

I will try out your suggestions tomorrow and let you know how things work.