Access Report Question

A vastly superior idea would be to perform this splitting in a query, and store the results in separate columns, and then let the report just pull the separate columns.

To get the last Zip Code (or should we say the last element in the series of values you could use Split() comibined with UBound() to identify and extract the last element that you are after (the zip code in this case.

For example...

Sub Sample()
    Dim str as String
    str = "COLE,GRACE|APT 908|6631 WAKEFIELD DRIVE||ALEXANDRIA|VA|22307"
    MsgBox Split(str, "|")(UBound(Split(str, "|")))
End Sub

That would probably work except the zip code can be the full 9 characters so I need to hack the last 4 characters off if it is.

If you want to insure that all you get is the 5 characters on the left you can do so with only one small modification.

Sub Sample()
    Dim str as String
    str = "COLE,GRACE|APT 908|6631 WAKEFIELD DRIVE||ALEXANDRIA|VA|22307"
    MsgBox Left(Split(str, "|")(UBound(Split(str, "|"))),5)
End Sub

I already have the function, and it works perfect. I've used it in excel manually, but I want the Report to run it by itself. If I put the string "COLE,GRACE|APT 908|6631 WAKEFIELD DRIVE||ALEXANDRIA|VA|22307" into the control source it works perfectly fine. Except that it does it for every row because I'm not getting the field value for each row.
So I just need the field value to give me a string so when I put in
=SplitMultiDelims([IP_SHPTO_ADDR],"|")
IP_SHPTO_ADDR is a string value of the field

1) could you post the code you're using in the procedure named SplitMultiDielims sot that it may be possible to offer a small change to what you have to render the result you're ultimately after

2) Is it safe to say that all addresses are separated by a space " "

3) To be clear, is the objective to get the 5 digit zip code that appears at the end of all addredsses and then combine those back together into a single string delimited by the pipe symbol "|"

Function SplitMultiDelims(Text As String, DelimChars As String) As String
Dim test
test = Split(Text, "|")
SplitMultiDelims = Mid$(test(6), 1, 5)
End Function

That's the function I'm using to split the string and give the first 5 characters of the zip code.
You can see a screenshot of what I'm trying to do at http://www.visionsofafar.com/msaccess.jpg

I went back to look over the question to try and figure out what part I was missing.  If I'm correct it was this sentance...

"This function works perfectly fine and gives me the returned value of 12345 when I put it in the Control Source property"

Suposing that the string value was field named "Address" your control source would look something like this...

=SplitMultiDelims([Address],"|")

You can however dow the same thing without using a custom function like the one you've created (which works fine by the way), by entering the following into the control source of say a text box so that it would read...

=Left(Split([Address], "|")(UBound(Split([Address], "|"))),5)

If my guess is right you're getting an error becaue of the absence of the equals sign "="

Was that the missing piece?

Strike the 2nd suggestion....

=Left(Split([Address], "|")(UBound(Split([Address], "|"))),5)

as it will not work within the control source of your form.  I just got done testing both ideas and the first one does work.  

One improvement you could make to your procedure is to eliminate the declaration of a variant by defining the string a string array and than extracting the value you are after that way as follows...

Function SplitMultiDelims(Text As String, DelimChars As String) As String
    Dim str() As String
    str = Split(Text, DelimChars)
    SplitMultiDelims = Left(str()(UBound(str)), 5)
End Function

Another version of the same function that's a little easier to read is one that drops a couple characters ()

Function SplitMultiDelims(Text As String, DelimChars As String) As String
    Dim str() As String
    str = Split(Text, DelimChars)
    SplitMultiDelims = Left(str(UBound(str)), 5)
End Function

In any case all work to accomplish the same end result.

I cut it down to this

Function SplitMultiDelims(Text As String) As String
Dim test = Split(Text, "|")
SplitMultiDelims = Mid$(test(6), 1, 5)
End Function

I then put the ControlSource property to "=SplitMultiDelims([IP_SHPTO_ADDR])"

It still gives me #error

Finally, I got it. What I had to do was create a separate object with the IP_SHPTO_ADDR as the value. The control was name Text17. I then went to the other control changed the control source to be "=SplitMultiDelims([Text17].Text)" and wallah. It worked.
Thank you Rick, you're the one that eventually got me there, so I'm giving you the points.

That would make sence.  You can't Dim the variable and assign it a value at the same time in this way.  The following revision should work for you...

Function SplitMultiDelims(Text As String) As String
    Dim test() As String
    test = Split(Text, "|")
    SplitMultiDelims = Mid$(test(6), 1, 5)
End Function

Set the control source to equal something like...

=SplitMultiDelims([Address])

Once again tested and this solution also works.

In the event that the Zip code is not always in the 6'th element of the array you may find the solution above presents a problem from time to time.  That is why the following revision of something posted previously would be preferable..

Function SplitMultiDelims(Text As String) As String
    Dim str() As String
    str = Split(Text, "|")
    SplitMultiDelims = Left(str(UBound(str)), 5)
End Function

The reason for this is that the UBound(str) function allows us to consistently grab the value stored in the last row of the array rather than hard coding it for element 6 in the array.  Sooner or latter you'll probably find the zip code appears in as element 5 or perhaps element 7 in which case the hardcoding of element 6 will cause a value other than the one intended to appear.  By using the UBound() function we are basically saying, "we don't care how many or how few elements there are, we just want the last one.