• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

Remove last 2 characters from a string

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
correlate
Asked:
correlate
  • 3
  • 2
  • 2
  • +3
1 Solution
 
Scott MadeiraCommented:
How about this:

strEmailAddress = Left(strEmailAddress, Len(strEmailAddress)-2 )
0
 
Ess KayEntrapenuerCommented:
Yourvariable.Replace("; "," ")
0
 
mbizupCommented:
At the end of the code:

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

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
IrogSintaCommented:
Me.Email Distribution = Left(strEmailAddress, Len(strEmailAddress)-2)
0
 
correlateAuthor Commented:
Brilliant - thanks for the speedy response
0
 
Ess KayEntrapenuerCommented:
Mine is shorter
0
 
correlateAuthor Commented:
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
 
mbizupCommented:
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
 
Ess KayEntrapenuerCommented:
Ahh true.  It was easier to type on my phone
0
 
IrogSintaCommented:
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
 
als315Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now