remove unwant characters at end of string

[kevcar40]

Hi
I have a table that contains a email addresses
i have concatinate the name along with a semi colon
this sting is then used in outlook to send out data
ie
email;email2;email3;

there can be upto 10 email addresses
how ever if there are only 5 email addresses i still have the surplus
semi colons
ie
email;email2;email3;email4;email5;;;;;

what i would like to do is remove the semi colons at the end of the
string
so i end up with
email;email2;email3;email4;email5

any ideas


thanks

kevin

 

[banem2]

Hi
I have a table that contains a email addresses
i have concatinate the name along with a semi colon
this sting is then used in outlook to send out data
ie
email;email2;email3;

there can be upto 10 email addresses
how ever if there are only 5 email addresses i still have the surplus
semi colons
ie
email;email2;email3;email4;email5;;;;;

what i would like to do is remove the semi colons at the end of the
string
so i end up with
email;email2;email3;email4;email5

any ideas

thanks

kevin

Create new query, switch to SQL view and paste this:

UPDATE myTable SET myTable.[myField] =
fRemoveSemiColon(myTable.myField);

Close and save query by some name like "qryUpdateEmailField".

Create new module and paste this code:

Function fRemoveSemiColon(strText)
If Len(strText) = 0 Then Exit Function
Do Until Right(strText, 1) <> ";"
strText = Left(strText, Len(strText) - 1)
Loop
fRemoveSemiColon = strText
End Function

Run query when you need it.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[kevcar40]

Hi
I have a table that contains a email addresses
i have concatinate the name along with a semi colon
this sting is then used in outlook to send out data
ie
email;email2;email3;

there can be upto 10 email addresses
how ever if there are only 5 email addresses i still have the surplus
semi colons
ie
email;email2;email3;email4;email5;;;;;

what i would like to do is remove the semi colons at the end of the
string
so i end up with
email;email2;email3;email4;email5

any ideas

kevin

Create new query, switch to SQL view and paste this:

UPDATE myTable SET myTable.[myField] =
fRemoveSemiColon(myTable.myField);

Close and save query by some name like "qryUpdateEmailField".

Create new module and paste this code:

Function fRemoveSemiColon(strText)
  If Len(strText) = 0 Then Exit Function
  Do Until Right(strText, 1) <> ";"
    strText = Left(strText, Len(strText) - 1)
  Loop
  fRemoveSemiColon = strText
End Function

Run query when you need it.

Regards,
Branislav Mihaljev, Microsoft Access MVP- Hide quoted text -

- Show quoted text -

thank you

 

[Krzysztof Pozorek [MVP]]

(...)

email;email2;email3;email4;email5;;;;;

what i would like to do is remove the semi colons at the end of the
string
so i end up with
email;email2;email3;email4;email5

any ideas

You may use Replace function in this way:

strList = "email;email2;email3;email4;email5;;;;;"
strResult = Replace(Trim(Replace(strList ,";"," "))," ",";")

K.P. MVP Poland
www.access.vis.pl