INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Replace only if the last character of a field is a specific character

Replace only if the last character of a field is a specific character

(OP)
Attempting to clean up a field that may contain a ; at the beginning of the field or the end of a field. Thought I could use the replace function with a right function but does not seems to be working.

How would I go about removing a character if it is only found at the beginning or ending of a field?

The expression I wrote that is not working is Replace(Right([Tbl_Customers]![email],1),";","")
The data looks like this bennie@abc.com;geoff@abc.com;

Needing to replace only the last ; in the address and want to replace it with nothing

Thanks
Bennie

RE: Replace only if the last character of a field is a specific character

Something like:
iif(right(tbl_customers!email,1)=";",left(tbl_customers!email,len(tbl_customers!email)-1),tbl_customers!email)

RE: Replace only if the last character of a field is a specific character

(OP)
Thanks sxscheck,

That will work but hoping someone can help me with the replace function. Perhaps replace will not work with a right finction.

RE: Replace only if the last character of a field is a specific character

No way to do it with the replace for several reasons

CODE -->

Replace(Right([Tbl_Customers]![email],1),";","") 

The replace function takes an input expression and then acts on it. But can only return the acted on expression

Replace( expression, find, replace [, start ] [, count ] [, compare ] )

Imagine your string is "abcdef;"
if you did
replace(right(Tbl_Customers]![email],1) it would return "a"
so you starting expression is "a" and you are going to then be doing this
replace("a",";","")
and you will get "a"

I would roll my own function and a lot easier to debug

CODE

Public Function RemoveSemi(varText As Variant) As String
  If Not IsNull(varText) Then
    RemoveSemi = varText
    If Left(RemoveSemi, 1) = ";" Then
      RemoveSemi = Mid(RemoveSemi, 2)
    End If
    If Right(RemoveSemi, 1) = ";" Then
      RemoveSemi = Left(RemoveSemi, Len(RemoveSemi) - 1)
    End If
  End If
End Function 
This will handle ";" on the beginning and end and leave ones in the middle. If you want to get rid of all ";" then this would be trivial.

replace(";abcd",";","",1,1)
would work for the above string because it says to to do one replacement starting on the string that starts at the first position of the original string. It would not work for this "abc;d" because it would also get rid of the first semicolon it comes across.
replace("abcd;",";","",len("abcd;"),1)
will not work to take the last ; off, although you might think. The start position does not do what you would think. It works more like a mid function.
To demonstrate
replace("12345;678",";","",3,1)
= 345678

RE: Replace only if the last character of a field is a specific character

Just to be on the safe side, I would add to MajP's code:

CODE

...
RemoveSemi = Trim(varText)
... 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Replace only if the last character of a field is a specific character

Good point.

RE: Replace only if the last character of a field is a specific character

If I understand correctly, I would use:

CODE --> SQL

UPDATE tbl_Customers
SET Email = Left(Email,Len(Email)-1)
WHERE Right(Email,1) = ";" 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Replace only if the last character of a field is a specific character

More like:

CODE

UPDATE tbl_Customers
SET Email = RemoveSemi(Email)
WHERE InStr(Email, ";") > 0 

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close