[AccessD] an ACCESS query

[Ryan W]

Whoops! That should probably be WHERE .... = 0 (strings are identical)...



On Tue, Mar 5, 2019 at 10:48 AM Ryan W <wrwehler at gmail.com> wrote:

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