Access 2010 case sensitive join

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;

Microsoft AccessSalesforce

Last Comment

Perfishent

8/22/2022 - Mon

peter57r

3/13/2012

I can't see anything logically wrong, but I would try a simpler query and moving the strcomp expression into the Where clause, to see that you are getting matches when you expect.

So I would try first....This should produce all case-sensitive matches

SELECT A.*, B.*
FROM tApexDataBridge As A Inner JOIN Salesforce_Accounts As B
ON A.SalesforceID = B.[Account ID]
WHERE STRCOMP(A.SalesforceID, B.[Account ID], 0) = 0

Perfishent

3/14/2012

ASKER

When I put the STRCOMP() function in the WHERE clause, I don't get any results at all.

SELECT 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]
WHERE (((Salesforce_Accounts.[Termination Date]) Is Null)) AND STRCOMP(tApexDataBridge.SalesforceID, Salesforce_Accounts.[Account ID], 0) = 0
ORDER BY tApexDataBridge.SalesforceID;

peter57r

3/14/2012

The point of my post was to try something simpler to establish whether any matches are being found, not to move strcomp within your original query.

Your help has saved me hundreds of hours of internet surfing.

fblack61

Perfishent

3/28/2012

ASKER

I simplified this down so that I'm not using a join but a sequence of dynamic update statements instead.

UPDATE tDataBridge SET [Salesforce Account Name] = 'Gerhart Cole Inc.', [Salesforce Account ID] = '001C000000q9Qgr' WHERE strcomp([Apex Salesforce Account ID],'001C000000q9Qgr',0) = 0

For some reason, the update statement is not updating the records when the id's match. Can you see anything wrong with the above SQL statement?

The Update statement is generated in the following loop:

Set rst2 = db.OpenRecordset(sqltext)
Do While Not rst2.EOF
If rst2.RecordCount > 0 Then
sqltext = "UPDATE tDataBridge SET [Salesforce Account Name] = '" & Replace(rst2![Account Name], "'", "''") & "', [Salesforce Account ID] = '" & rst2![Account ID] & "' WHERE strcomp([Apex Salesforce Account ID],'" & rst2![Account ID] & "',0) = 0"
DoCmd.RunSQL (sqltext)
End If
rst2.MoveNext
Loop
rst2.Close

ASKER CERTIFIED SOLUTION

Perfishent

3/29/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Perfishent

4/3/2012

ASKER

This was the only solution presented that worked.