Solved

Access Report Question

Posted on 2007-11-15
15
224 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

28 Experts available now in Live!

Get 1:1 Help Now