?
Solved

Access 2010 case sensitive join

Posted on 2012-03-13
6
Medium Priority
?
1,871 Views
Last Modified: 2016-02-17
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;
0
Comment
Question by:Perfishent
  • 4
  • 2
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37715423
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
1
 

Author Comment

by:Perfishent
ID: 37720417
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;
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37720691
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:Perfishent
ID: 37778901
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
0
 

Accepted Solution

by:
Perfishent earned 0 total points
ID: 37783279
I figured this problem out. Here's the code I used to get the join to work:

    sqltext = "SELECT tDataBridge.Apex_Company, tDataBridge.Client_No, tDataBridge.PR_Spec, tDataBridge.[Apex Salesforce Account ID], Salesforce_Accounts.[Account ID]," _
    & " tDataBridge.[Apex Client Name], Salesforce_Accounts.[Account Name], '' as Exact_Name_Match, tDataBridge.[Apex Term Date] INTO tDataBridgeCheck" _
    & " FROM tDataBridge LEFT JOIN Salesforce_Accounts" _
    & " ON (((StrComp(Trim([tDataBridge].[Apex Salesforce Account ID]),Trim([Salesforce_Accounts].[Account ID]),0))=0) AND ((Salesforce_Accounts.[Parent Account ID]) Is Null))"
0
 

Author Closing Comment

by:Perfishent
ID: 37800023
This was the only solution presented that worked.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question