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
Solved

Access Report Question

Posted on 2007-11-15
15
228 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Rick
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

839 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