selective importing query

[Dylan Snyder]

I get an excel report from outside the company that contains a text field that can have a range of values but all we want to import is the letter code before the / sign. The field may be CFS or CFS/CY or CY/CY/CY or anything but all we would want to import in each case is the data before the fist slash. What in your opinion is the best way? I was using a lot of If statements, but I really have no way of knowing every permutation of this data. Any thoughts?

 

[jal]

I get an excel report from outside the company that contains a text field that can have a range of values but all we want to import is the letter code before the / sign. The field may be CFS or CFS/CY or CY/CY/CY or anything but all we would want to import in each case is the data before the fist slash. What in your opinion is the best way? I was using a lot of If statements, but I really have no way of knowing every permutation of this data. Any thoughts?

If you import that column into a column called word, you may be able to extract the material before the slash like this:

Select Left(word, instr(word, "/") -1)

or use the above in an Update statement:

UPDATE tABLE1 SET word = Left(word, instr(word, "/") -1)

 

[MSAccessRookie]

If you import that column into a column called word, you may be able to extract the material before the slash like this:

Select Left(word, instr(word, "/") -1)

or use the above in an Update statement:

UPDATE tABLE1 SET word = Left(word, instr(word, "/") -1)

I tried that approach, and it did not work whenever there was no "/" in the value of word. The following change covers that possibility:

SELECT IIf(instr(word,"/")=0, word, Left(word,instr(word,"/")-1))

 

[Dylan Snyder]

thanks a lot. I know only the basics of SQL. I am doing this in the graphical access query. Do you know the appropriate syntax using access instead of SQL?

 

[jal]

thanks a lot. I know only the basics of SQL. I am doing this in the graphical access query. Do you know the appropriate syntax using access instead of SQL?

Did you try pasting his code into sql view, and then saving it? Sometimes Access will convert it to graphical layout when you do so.

 

[jal]

Well, I don't know your table name, but you would have to do something like this, in sql view

SELECT IIf(instr(word,"/")=0, word, Left(word,instr(word,"/")-1))
FROM tABLE1