Matching two text columns

[wdbouk]

Hello,

I have two text columns but for some reason one of them has the following format " 1234AAT' and the other 1234AAT. When I try to match them I receive 0 match. I am wondering how to get rid of the "" and ' in the
" 1234AAT' format or how to match the two text columns?
Thx

 

[belovedcej]

use Like "*"&[contents]&"*" instead of equals

 

[wdbouk]

could u clarify more? I am receiving errors with my interpretations.
assume i have table A qand table B . how to compare A.cusip with B.Cusip?

 

[belovedcej]

Maybe I'm misunderstanding you.

Are you building a query to show only results where the two columns are the same?

If that is the case, I would build a normal query with the join between the two tables on that field.

Then go into the SQL view of the query.

In the "WHERE" clause, put something like:

WHERE (tableA.cusip LIKE "*"&[tableB.cusip]&"*")

the * is the wild card character, indicating that there is stuff before and after the match.

 

[lespaul]

you mean the tick marks ' and " are in the field value?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[wdbouk]

yes tick marks ' and " are in the field value. Thanks belovedcej ur method works

 

[wdbouk]

Actually it is not working. it is returning all the values of tableA.cusip without matching the tableB.cusip
the query is:
SELECT Prices2.Cusip, Prices2.Date, Prices2.Price, Prices2.Accrued, Prices2.Yield, Prices2.Duration, Prices2.Convexity, Selected.*
FROM Prices2 LEFT JOIN Selected ON Prices2.Cusip = Selected.Cusip
WHERE Prices2.Cusip LIKE "*"&[Selected.Cusip]&"*";
ANything wrong?

 

[PHV]

SELECT Prices2.Cusip, Prices2.Date, Prices2.Price, Prices2.Accrued, Prices2.Yield, Prices2.Duration, Prices2.Convexity, Selected.*
FROM Prices2 LEFT JOIN Selected ON Prices2.Cusip = '" ' & Selected.Cusip & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wdbouk]

nope.
the same problem. all observations from Prices2 are reported and none of the selected table

 

[ceh4702]

Maybe you can make an update routine to get rid of the tick marks. That is the root of the problem. Some program is probably written incorrectly and it puts tick marks in there. You need to identify what is causing this. Might be the users enter something wierd and then the program that reads the input is not getting rid of the user errors correctly.

If you do not like my post feel free to point out your opinion or my errors.