[AccessD] an ACCESS query

[McGillivray, Don]

I can see from the responses of others that I misunderstood the point of your question.  I took you to mean that you wanted the join to work regardless of whether there was an exact match.  If you want only those records where there is an exact case match, then Ryan is right.  You need the additional argument in the StrComp() function to make the comparison binary.  Then test for a result of 0.

Sorry for muddying the waters.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Tuesday, March 05, 2019 8:28 AM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] an ACCESS query

Hi Mary,

Access is case-insensitive by default, so if your data resides in native Access tables you should be able to compare values without concern for case - unless you've altered the default (not sure if that's possible).  Or if your data is in tables linked from some other DBMS (Oracle, SQL Server, etc.) you may need to abide by the case-sensitivity of the source DBMS.

In either case, if you want to be sure that you're comparing your values in the same case, you can do a case conversion in the ON clause of the JOIN to force both values to be compared using the same case:

SELECT [I&P-44-4-accounts].[Account ID], [I&P-44-4-contacts].[Account ID]FROM [I&P-44-4-accounts] INNER JOIN [I&P-44-4-contacts] ON UCase([I&P-44-4-accounts].[Account ID]) = UCase([I&P-44-4-contacts].[Account ID]);

Hope this helps.

Don

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mary Davis
Sent: Tuesday, March 05, 2019 5:31 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] an ACCESS query


I posted my question to a different list yesterday -- perhaps the wrong listserv.With apologies, I'll repost here.
I'm trying to use a query to match 2 tables: accounts and contacts.The account ID is case sensitive.  (it comes from SalesForce.) I thought I found a way to add criteria to a simple query to look for case sensitive ID matches.  But I'm not able to figure out how to run this.Is this possible?  If so, can you provide me some instructions? This is what I came up with.
SELECT [I&P-44-4-accounts].[Account ID], [I&P-44-4-contacts].[Account ID]FROM [I&P-44-4-accounts] INNER JOIN [I&P-44-4-contacts] ON [I&P-44-4-accounts].[Account ID] = [I&P-44-4-contacts].[Account ID]WHERE (((StrComp([I&P-44-4-accounts.account id],[I&P-44-4-contacts].[account id]))=0));

thanks very much for your help.
MaryWilmington Delaware
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

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