How to truncate a string in Access

September 20, 2006 9:15 AM

I'm writing an query in MS Access to get column 1 = product ID, column 2 = product Description. However, the description field has addtional spec data that I don't need. I want to remove anything after the first word, meaning remove after the first blank space. Example: Prod ID      Prod Desc 10-362-3     Packing DT 12-345-5     Seal XT L-23 Desired result: 10-362-3     Packing 12-345-5     Seal I tried to use FIND or SEARCH functions to identify the position of the first blank space and to truncate everything after that. However, Access does not recoganize either FIND or SEARCH. Does anyone have any idea how to accomplish this? Thanks.
posted by dy to Computers & Internet (4 answers total)

Best answer: INSTR is the function you need.

UPDATE products SET ProdDesc = LEFT(ProdDesc, INSTR(ProdDesc, ' '))

You may have to use INSTR(ProdDesc, ' ') - 1 to remove the space from the column - I don't have Access handy to check right now.
posted by pocams at 9:18 AM on September 20, 2006

Yes, you will - I just modeled your sample data in Access and this did what you want:

UPDATE products SET ProdDesc = LEFT(ProdDesc, INSTR(ProdDesc, ' ') - 1)

posted by pocams at 9:21 AM on September 20, 2006

Response by poster: Thanks, pocams. I tried INSTR, and it worked. But I have another issue here - some description strings only have one word. INSTR will erase the whole string in this case. I guess I can use a conditional check to get around that.
posted by dy at 10:23 AM on September 20, 2006

Yup, just a WHERE clause.

UPDATE .... WHERE INSTR(ProdDesc, ' ') > 0

posted by pocams at 7:55 PM on September 20, 2006