Truncate field starting with location of specific characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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 "*--*"
 
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.
 
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.
 
Back
Top