?
Solved

Remove last 2 characters from a string

Posted on 2012-09-20
11
Medium Priority
?
466 Views
Last Modified: 2012-09-20
Dear Experts

I have a piece of code which goes through various fields (containing individual email addresses) and joins them up in a string (strEmailAddress). It then saves the result it to a field called Me.EmailDistribution. The problem is that the string always ends in "; " & I need to get rid of it

So I need some way of removing just the last 2 characters (a ";" and a space) from the string once it has collated the addresses

The code is below, can anyone help?

Dim strEmailAddress As String
strEmailAddress = ""
If Me.ConsEmail1 = "" Or IsNull(Me.ConsEmail1) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail1 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail2 = "" Or IsNull(Me.ConsEmail2) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail2 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail3 = "" Or IsNull(Me.ConsEmail3) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail3 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail4 = "" Or IsNull(Me.ConsEmail4) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail4 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail5 = "" Or IsNull(Me.ConsEmail5) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail5 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail6 = "" Or IsNull(Me.ConsEmail6) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail6 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail7 = "" Or IsNull(Me.ConsEmail7) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail7 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail8 = "" Or IsNull(Me.ConsEmail8) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail8 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail9 = "" Or IsNull(Me.ConsEmail9) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail9 & "; "
End If
 '--------------------------------------------------------------------------------------------------
If Me.ConsEmail10 = "" Or IsNull(Me.ConsEmail10) Then
strEmailAddress = strEmailAddress
Else
strEmailAddress = strEmailAddress & Me.ConsEmail10 & "; "
End If
 '--------------------------------------------------------------------------------------------------
Me.EmailDistribution = strEmailAddress

Open in new window

0
Comment
Question by:correlate
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 14

Accepted Solution

by:
Scott Madeira earned 2000 total points
ID: 38417448
How about this:

strEmailAddress = Left(strEmailAddress, Len(strEmailAddress)-2 )
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38417453
Yourvariable.Replace("; "," ")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38417454
At the end of the code:

strEmailAddress  = Left(strEmailAddress , len(strEmailAddress ) -2)

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:IrogSinta
ID: 38417455
Me.Email Distribution = Left(strEmailAddress, Len(strEmailAddress)-2)
0
 

Author Closing Comment

by:correlate
ID: 38417465
Brilliant - thanks for the speedy response
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38417517
Mine is shorter
0
 

Author Comment

by:correlate
ID: 38417541
hi esskayb2d

I cracked on with the first solution and it worked all good before I had a change to relook at the others, I may well be wrong but I think your solution would remove all the ";" & spaces rather than just the last one
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38417551
esskayb2d,

The accepted solution provided by smadeira is the best answer here, as it only removes the last two characters.

Replace will remove ALL occurrences of "; " in the email string, which is not desirable if there are multiple email addresses, and I don't think the syntax posted is valid in Access VBA (looks like VB.Net?).
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38417582
Ahh true.  It was easier to type on my phone
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38417607
You could shorten your code a bit this way:
    Dim strEmailAddress As String

    If Me.ConsEmail1 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail1 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
    If Me.ConsEmail2 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail2 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
    If Me.ConsEmail3 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail3 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
      If Me.ConsEmail4 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail4 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
         If Me.ConsEmail5 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail5 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
         If Me.ConsEmail6 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail6 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
         If Me.ConsEmail7 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail7 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
         If Me.ConsEmail8 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail8 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
         If Me.ConsEmail9 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail9 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
        If Me.ConsEmail10 & "" <> "" Then
        strEmailAddress = strEmailAddress & Me.ConsEmail10 & "; "
    End If
     '--------------------------------------------------------------------------------------------------
     
    Me.Email Distribution = Left(strEmailAddress, Len(strEmailAddress) - 2)

Open in new window

And quite a bit more even this way:
    Dim strEmailAddress As String
    Dim i As Byte
    
    For i = 1 To 10
        If Me("ConsEmail" & i) & "" <> "" Then
            strEmailAddress = strEmailAddress & Me("ConsEmail" & i) & "; "
        End If
    Next
    
    Me.Email Distribution = Left(strEmailAddress, Len(strEmailAddress) - 2)

Open in new window

0
 
LVL 40

Expert Comment

by:als315
ID: 38417903
Ron: very good solution, But I prefer to empty string before adding new data:
Dim strEmailAddress As String
    Dim i As Byte
    strEmailAddress = ""
    For i = 1 To 10
        If Me("ConsEmail" & i) & "" <> "" Then
             if len(strEmailAddress) > 0 then strEmailAddress = strEmailAddress & "; "
             strEmailAddress = strEmailAddress & Me("ConsEmail" & i)
        End If
    Next

Open in new window

You don't need to remove last symbols
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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: …
Suggested Courses

864 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