Solved

Access Report Question

Posted on 2007-11-15
15
219 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
  • 9
  • 5
15 Comments
 
LVL 65

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 500 total points
ID: 20292430
I'm delighted to see it worked out for you.  Happy Coding!!!  :)

Rick
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now