[AccessD] Query Linking Unlike Fields

I've pulled this off (in Access 97) by going to the SQL view of the query
and converting one of the fields to the matching datatype,

SELECT tblMyTable.AffiliateID, tblMyTable.Desc, tblPayPal.Desc
FROM tblMyTable INNER JOIN tblPayPal ON tblMyTable.AffiliateID =
clng(tblPayPal.AffiliateID);

The drawback to this is while the clng is in the SQL of the query, the join
can't be represented in design view.  I would imagine with large tables you
would see a performance hit but I haven't done any testing.

HTH
Rusty

-----Original Message-----
From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com]
Sent: Wednesday, May 03, 2006 8:51 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Query Linking Unlike Fields


Dear List:

The affiliate ID in our affiliate program is a long integer - as it 
should be.  However, the affiliate ID is recorded from the PayPal 
transaction in a text field.  So When I try to link them in a query to 
show affiliate sales by affiliate name, of course, I get a data type 
mismatch.  Is there a way around this short of altering the tables?

MTIA,

Rocky

-- 
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************