[AccessD] an ACCESS query

[McGillivray, Don]

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