[AccessD] an ACCESS query

[Ryan W]

I'm curious if the case sensitivity matters, if StrComp would help

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/strcomp-function

Something like

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].[AccountID],[I&P-44-4-contacts].[AccountID], 0)
<> 0;





On Tue, Mar 5, 2019 at 10:28 AM McGillivray, Don <DMcGillivray at ctc.ca.gov>
wrote:

> 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
>