Truncate field starting with location of specific characters

[Guest]

Hi,

I need to truncate a field once a couple of special characters are found.

For example, if the field stores people's names and if I find two
consecutive dashes anywhere in the field (searching from left to right), I
want to clear the contents of the field starting with the two dashes.

Anything preceding the two dashes in the field will need to be preserved.

Thanks!

 

[Douglas J. Steele]

In VBA, it would be something like:

Dim lngDashes As Long

lngDashes = InStr(Me.Textbox1, "--")
If lngDashes > 0 Then
Me.Textbox1 = Left(Me.Textbox1, lngDashes - 1)
End If

In a query, it would be something like

UPDATE MyTable
SET MyField = Left(MyField, Len(InStr(MyField, "--")) - 1)
WHERE MyField LIKE "*--*"

 

[Guest]

MYSTRING = Left(MYSTRING, InStr(MYSTRING, "--") - 1)

 

[Guest]

Thanks very much!

By the way, what does the -1 do?

 

[Guest]

Thanks very much!

By the way, what does the -1 do?

 

[Douglas J. Steele]

InStr finds the location of the first occurrance of --. You want to only
keep what's before that position, so you subtract 1 from that value.

 

[Guest]

the instr function returns the starting character of the string we're
searching for. For example ( using the function below ) we ran "123--", the
instr() function would return 4. However, we only want the first 3
characters of the string.. thus - 1.