Trimming a string with an update query - Help

[Joe Williams]

I have a database with a description field. Some of the descriptions have
the # symbol in the first couple of characters signifying a part approval. I
want to remove this # sign.

So, what i would like to to is to scan the first 5 characters of the
description, and if the # sign is found, then to remove the # sign and leave
the rest of the description.

The only caveat is that I only want the first 5 characters of the
description to be searched, as there may be # signs later in the description
that are valid. For isntance:

# BLACK PULLEY should be BLACK PULLEY
C/C # GEAR should be C/C GEAR
LARGE GEAR #1 SIZE should be LARGE GEAR #1 SIZE

Any ideas? Thanks!

Joe

 

[Van T. Dinh]

UPDATE [YourTable]
SET [ItemDesc] = Left( [ItemDesc], InStr(1, [ItemDesc], "#") - 1 ) &
Mid( [ItemDesc], InStr(1, [ItemDesc], "#") + 1)
WHERE InStr(1, [ItemDesc], "#") BETWEEN 1 AND 5

 

[John Vinson]

I have a database with a description field. Some of the descriptions have
the # symbol in the first couple of characters signifying a part approval. I
want to remove this # sign.

So, what i would like to to is to scan the first 5 characters of the
description, and if the # sign is found, then to remove the # sign and leave
the rest of the description.

The only caveat is that I only want the first 5 characters of the
description to be searched, as there may be # signs later in the description
that are valid. For isntance:

# BLACK PULLEY should be BLACK PULLEY
C/C # GEAR should be C/C GEAR
LARGE GEAR #1 SIZE should be LARGE GEAR #1 SIZE

Any ideas? Thanks!

The blank after the # may be a problem. Might you have a record like

C/C #1 GEAR

and if so, what should it be changed TO?

Try first using a query to find the records which need changing: use a
query

SELECT Description FROM yourtable
WHERE InStr("#", [Description]) > 0 AND InStr("#", [Description]) <= 5

The InStr will be zero if the field contains no # at all.

Then on this query, update the field to

Left([Description], InStr([Description], "#") - 1) &
Mid([Description], InStr([Description], "#") + 1)

This will JUST remove the # - leaving " BLACK PULLEY" or "C/C GEAR".
If all of the octothorpes to be removed have a trailing blank use

InStr([Description], "# ")

in all instances, and change the Mid() function to use +2 instead of
+1.

John W. Vinson[MVP]