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

[Bennie47250]

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]!,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

 

[sxschech]

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

 

[Bennie47250]

Thanks sxscheck,

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

 

[MajP]

No way to do it with the replace for several reasons

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]!,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

[/code]
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

 

[Andrzejek]

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

...
RemoveSemi = [blue]Trim([/blue]varText[blue])[/blue]
...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Good point.

 

[dhookom]

If I understand correctly, I would use:

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

 

[Andrzejek]

More like:

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

Have fun.

---- Andy

There is a great need for a sarcasm font.