Using Function to return an array?

[Guest]

I want to parse a string into three parts. I'm considering three approaches:

1) Global variables with a Sub to fill the values.
2) Three separate functions, each returning one part of the parsed string.
3) A function to return a three-element array.

Of these three approaches, I think 3 is the most compact and easy to follow,
but what is eluding me is how do I get a Function to return an array? It
seems possible but I find no documentation on how to do this, and my approach
is failing. Don't get bogged down in the details of my Function. The key is
that I had hoped to populate the array via ParseMemoNumber(array element
number)= some operation...
What happens in practice is that as soon as the Function gets to the first
assignment, ParseMemoNumber(3) = Trim(Right(strMemoNumber,
Len(strMemoNumber) - LastDashPosition - 3))
it calls the function again i.e. it calls itself recursively. That won't do.

Don't spend a lot of time on this, the Function is easy enough to use the
other two approaches, but my preference is to use a single function. Perhaps
someone has a quick answer as to whether or not this is possible and if it
is, toss a sample over the fence. If its possible, this SHOULD be a
cakewalk, but it eludes me at the moment.
FYI: The Function should accept a string in the form of
String1-String2-String3.


Thanks

 

[Guest]

Hi, LaTanya.

If you are using Access 2000 or later, the Split function will return an
array of substrings for you:

From Access Help topic:

Returns a zero-based, one-dimensional array containing a specified number of
substrings.
Syntax
Split(expression[, delimiter[, limit[, compare]]])

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.

 

[Guest]

Hi LaTanya,

The earlier post will likely be the easiest way to do what you want to do,
but just for general information, you can return an array from a function.
To do it, you define the function result as a variant. Within the function,
dimension an array variable as you normally would (with a different name than
the function), and fill the elements within the code. Then, at the end, you
set the function variable (name) equal to the array without using parenthesis
on either one. This sets the function variant equal to the entire array
variable that you filled in your code, and returns it to the calling
procedure.

HTH, Ted Allen