Joining tables when field has blanks

[ncchish]

I'm using CE11. I'm trying to join a table where the tax number on one of the tables has spaces in front of it. The other table uses a regular tax number - no spaces. Can you tell me how to join these tables? So far, I'm not getting an data at all. The join is inner and I've added this to my R/S but it didn't produce any results.

trimright({APR.MSTR_TAX_ID}) = {AGT.TAX_ID_CHAR}

Thanks for any advice.

 

[Turkbear]

Hi,
Spaces in front of the first char or after the chars?
If both are text ( character) fields ( If your database is Oracle, if both are VARCHAR2 fields), A left-outer join between the tables should work.

Otherwise try using a Command instead of selecting tables and use :
Where Trim(APR.MSTR_TAX_ID) = Trim(AGT.TAX_ID_CHAR)

If all else fails, use a subreport for the second table's data and place it next to the details fields from the first table..You can use a formula to link the report to the Sub.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ncchish]

Hi Turkbear,

The space is in front of the first char on one field. The database is db2p. I will try the subreport and let you know if it works. Thanks.