Truncate end of a string

S

sudonim

Hello all, I have a update query that I would like to remove the last 6
characters from a string where the string contains UN#### with ####
being 4 numbers in a row. Not all of the fields contain this. The code
I have below is able to find the correct strings but it deletes
everything in the field. Is there someone out there than can help me to
fix this so it doesnt delete everything?

UPDATE ExportsUnprocessed SET Commodity =
Left("Commodity",InStr(1,"Commodity",-6))
WHERE (((Commodity) Like "*UN####"));


Thanks in advance!
 
D

Dirk Goldgar

sudonim said:
Hello all, I have a update query that I would like to remove the last
6 characters from a string where the string contains UN#### with ####
being 4 numbers in a row. Not all of the fields contain this. The code
I have below is able to find the correct strings but it deletes
everything in the field. Is there someone out there than can help me
to fix this so it doesnt delete everything?

UPDATE ExportsUnprocessed SET Commodity =
Left("Commodity",InStr(1,"Commodity",-6))
WHERE (((Commodity) Like "*UN####"));


Thanks in advance!
Click to expand...

That pattern expression will find records where Commodity *ends in*
"UN####", not just records where the field contains that pattern.
That's different from what you said above; is that what you intended?
If not, you may want to say

Like "*UN####*"

instead.

Assuming it does select the correct records, you probably want to do
this:

UPDATE ExportsUnprocessed
SET Commodity = Left(Commodity, Len(Commodity) - 6)
WHERE Commodity Like "*UN####";

Be sure to make a backup of the table before you try this out!
 
S

sudonim

Dirk,

Thanks so much for your help. Your solution worked! I was looking for
records where commodity ends in "UN####". Thanks again,
Colin
 
Top