How to trim the characters from a string? 1

[123blyzzard]

HI,

I have a field named Colors in a table A and The same field Colors in Table B. But in table A, I have "Red Color", "Blue Color" and "Green Color" and in table B only "Red", "Green" and "Blue". Now I need to connect table A to Table B on Colors and thus need to remove " Color" from table A first in order to join them.

I used Update tbl_name set case when Colors ="Red Color" then Color="Red" but that is not working.

I also used replace(iif[Colors="Red Color"], " Color","")
but still not working.

Any suggestions would be appreciated.
Thanks,

Blyzzard

 

[DrSimon]

I think this come's under the heading "If I was going there I wouldn't have started from here" .

But assuming that 'Color' is always preceded by one space you could create an intermediate query with a new column of:
Colors_New: Left(A![Colors],Len(A![colors])-6)
And then join using B![Colors] to Colors_New in this query

Is that what you want?

Simon Rouse

 

[123blyzzard]

Do i need to use subquery to create a new column?

 

[DrSimon]

Yes I was suggesting a subquery. Don't know how many columns you have in Table A, but to make it easy I would include all the columns of A in a new query and include that new column. Then save that query and join table B to it.
Simon Rouse

 

[traingamer]

A simple

replace([Colors], " color", "")

should work just fine. No need for an immediate if or anything else.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[123blyzzard]

Thank you all,
I really appreciate your help.

I created an update query and used the left and len function to get rid of space and Color string.
But to make this query more generalized in case if there are different strings after space like "Red Signal" or "Red Color" in the same column on different rows and If I want to grab only "Red" value and get rid of space and string after space, i.e if i need to select the first word that comes before space, then do I need to count till I get a space?Also Which function do I need to use???

Thanks,
Blyzzard

 

[PHV]

Left([Colors], InStr([Colors] & " ", " ")-1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[123blyzzard]

Thanks..It worked