?
Solved

Access Report Question

Posted on 2007-11-15
15
Medium Priority
?
234 Views
Last Modified: 2013-11-28
I'm trying to create a report which has four fields. One of these fields has an address crammed into one field with the values seperated by |.
I created a function to split the string into their individual values and then give me the zip code. However, I can't get it to work in the Report.

The value in the table looks like this
COLE,GRACE|APT 908|6631 WAKEFIELD DRIVE||ALEXANDRIA|VA|22307

I'm trying to get the very last value.

This function works perfectly fine and gives me the returned value of 12345 when I put it in the Control Source property
=SplitMultiDelims("12345|12345|12345|12345|12345|12345|12345|12345","|")
This function gives me #error when I put it in the Control Source property
=SplitMultiDelims([IP_SHPTO_ADDR],"|")

So basically I think the text isn't getting through, so I need to know how I get the string value of a field value in a report when it's gathered by a query.
0
Comment
Question by:jamesr1411
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
15 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20291447
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.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20291485
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
0
 

Author Comment

by:jamesr1411
ID: 20291509
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20291554
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
0
 

Author Comment

by:jamesr1411
ID: 20291556
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
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20291677
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 "|"
0
 

Author Comment

by:jamesr1411
ID: 20291731
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
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20292027
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?
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20292127
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
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20292142
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.
0
 

Author Comment

by:jamesr1411
ID: 20292244
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
0
 

Author Comment

by:jamesr1411
ID: 20292370
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.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20292376
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.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20292419
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.
0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 2000 total points
ID: 20292430
I'm delighted to see it worked out for you.  Happy Coding!!!  :)

Rick
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question