Access 2010 case sensitive join
Posted on 2012-03-13
I've imported some Salesforce data into Access 2010 to join with data from a system called Summit Apex. We've added the Salesforce account id to Summit Apex in order to be able to integrate the two systems. The problem I'm having is that Salesforce account id's are case sensitive, but Access joins are not case sensitive.
For example, the id 0018000000YzcBe is not the same as the id 0018000000YzcBE in Salesforce. When I use the strcomp() function to make the left join case sensitive, I only get results back from the left table of the join and nothing from the right table. If I don't use the strcomp() function, then I get rows joined that should not be joined.
SELECT DISTINCT tApexDataBridge.Apex_Company, tApexDataBridge.Client_No, tApexDataBridge.PR_Spec, Salesforce_Accounts.[Parent Account ID], tApexDataBridge.SalesforceID, tApexDataBridge.Client_Name AS [Apex Account Name], Salesforce_Accounts.[Account Name] AS [Salesforce Account Name], tApexDataBridge.Term_Date AS [Apex Term Date]
FROM tApexDataBridge LEFT JOIN Salesforce_Accounts ON tApexDataBridge.SalesforceID = Salesforce_Accounts.[Account ID] AND STRCOMP(tApexDataBridge.SalesforceID, Salesforce_Accounts.[Account ID], 0) = 0
WHERE (((Salesforce_Accounts.[Termination Date]) Is Null))
ORDER BY tApexDataBridge.SalesforceID;