Removing characters after tilda sign

[AJK]

Hi,

I am trying to remove all characters after a tilda sign:

original field text: 86.HCGS.100794..GTEW~MPVERC
desired field text: 86.HCGS.100794..GTEW

I created the following module, but it does not recognize the "*" wildcard
and doesn't work, any ideas?

Option Compare Database

Function TildaReplace(sInput As String) As String

Dim s() As String, i As Long
Dim sDelim As String
sDelim = "~*| "
s = Split(sDelim, "|")
For i = LBound(s) To UBound(s)
sInput = Replace(sInput, s(i), "")
Next

TildaReplace = sInput

End Function

Thank you.

 

[John Spencer]

You say you want to remove all characters after the tilde. You should be
able to do that with something like the following.

IIF(TheText Like "*~*,Left(TheText,Instr(1,TheText ,"~")-1),TheText)

Your code has several errors in functionality
Split creates an array based on the character you designate as the splitting
character. So splitting "~*|" on th "|" character will return a one item
array consisting of "~*"

Replace will replace every occurence of that string with a space (or
whatever you designate). Replace does not recognize wild card characters as
wild cards - they are just another character.. Since "~*" does not appear
in the string, nothing is going to be replaced.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ken Sheridan]

Try this:

Function TildeReplace(sInput As String) As String

Dim intTildePos as Integer

intTildePos = Instr(sInput,"~")

If intTildePos > 0 Then
TildeReplace = Left(sInput, intTildePos-1)
Else
TildeReplace = sInput
End If

End Function

The character is a tilde, not a tilda BTW.

Ken Sheridan
Stafford, England

 

[Klatuu]

You are making in harder than it really is. All you need is:

Function TildaReplace(sInput As String) As String

TildaReplace = left(sInput,instr(sInput,"~")-1)

End Function

 

[raskew via AccessMonster.com]

Hi -
Try (this from the immediate (debug) window):

x = "86.HCGS.100794..GTEW~MPVERC"
y = left(x, instr(x, "~")-1)
? y
86.HCGS.100794..GTEW

HTH - Bob

 

[John W. Vinson]

Hi,

I am trying to remove all characters after a tilda sign:

original field text: 86.HCGS.100794..GTEW~MPVERC
desired field text: 86.HCGS.100794..GTEW

I created the following module, but it does not recognize the "*" wildcard
and doesn't work, any ideas?

Option Compare Database

Function TildaReplace(sInput As String) As String

Dim s() As String, i As Long
Dim sDelim As String
sDelim = "~*| "
s = Split(sDelim, "|")
For i = LBound(s) To UBound(s)
sInput = Replace(sInput, s(i), "")
Next

TildaReplace = sInput

End Function

Thank you.

If you just want to permanently discard the characters after the tilde, back
up your database and run an Update query updating the field:

UPDATE yourtable
SET [fieldname] = Left([fieldname], InStr([fieldname], "~") - 1)
WHERE [fieldname] LIKE "*~*";

No VBA code is needed.

John W. Vinson [MVP]