Solved How do I remove a specific character at it's last occurrence in a string, from a query?

[Sampoline]

I want to be able to remove the last occurrence of "/" from data I have in a query.. like below:

Original Data Example:
X/Y/A1/
X/Y/B1/
X/Y/B2/
X/Y/B4/
X/Y/B7a
X/Y/B8a
X/Y/B3/
X/Y/B3/
X/Y/B9a
X/Y/B4/

Required Data Example:
X/Y/A1
X/Y/B1
X/Y/B2
X/Y/B4
X/Y/B7a
X/Y/B8a
X/Y/B3
X/Y/B3
X/Y/B9a
X/Y/B4

What sort of expression should I use? I've tried doing a few Replace expressions, but couldn't get it right. Any help would be appreciated. Thanks.

 

[Isaac]

If X/Y/B7a becomes X/Y/B7a, then you don't want to remove the last occurrence of /

 

[Sampoline]

If X/Y/B7a becomes X/Y/B7a, then you don't want to remove the last occurrence of /

Yep that's my confusion now. So how can I only remove the ones that are reflected as so on my list?

 

[MajP]

You can do with iif, here it is with a UDF

Public Function RemoveSlash(varText As Variant) As String
  If Not IsNull(varText) Then
    If Right(varText, 1) = "/" Then varText = Left(varText, Len(varText) - 1)
    RemoveSlash = varText
  End If
 End Function

 

[theDBguy]

Maybe something like

IIf(Right([FieldName],1)="\",Left([FieldName],Len(FieldName)-1),[FieldName])

LOL. Too slow! (using my phone...)

 

[MajP]

In @theDBguy solution. Make sure to change "\" to "/"

 

[moke123]

Glad I hit refresh before posting

 

[theDBguy]

In @theDBguy solution. Make sure to change "\" to "/"

Oops!

 

[Isaac]

Yep that's my confusion now. So how can I only remove the ones that are reflected as so on my list?

Well it's even more confusing if you mis-state your requirement, or when your stated requirement vs. your sample data represent a contradiction.

I think what you want to say is: If the last character is /, then I want to remove /, otherwise leave the original value intact.

 

[Sampoline]

You can do with iif, here it is with a UDF

Public Function RemoveSlash(varText As Variant) As String
  If Not IsNull(varText) Then
    If Right(varText, 1) = "/" Then varText = Left(varText, Len(varText) - 1)
    RemoveSlash = varText
  End If
End Function

Maybe something like

IIf(Right([FieldName],1)="\",Left([FieldName],Len(FieldName)-1),[FieldName])

LOL. Too slow! (using my phone...)

Thank you everyone for your help!

I hadn't explored the function for it, this is very useful for another issue I had with another project, so thank you for this solution.

I didn't know about IIf in the query DB, thanks for that. This worked best for my current issue.

 

[Sampoline]

Well it's even more confusing if you mis-state your requirement, or when your stated requirement vs. your sample data represent a contradiction.

I think what you want to say is: If the last character is /, then I want to remove /, otherwise leave the original value intact.

My apologies, thankyou for the correction. I'll be a bit more careful next time.

 

[theDBguy]

Thank you everyone for your help!

I hadn't explored the function for it, this is very useful for another issue I had with another project, so thank you for this solution.

I didn't know about IIf in the query DB, thanks for that. This worked best for my current issue.

Hi. Glad to hear you got it sorted out. Good luck with your project.