Re: [MS_AccessPros] query when there are record that differ for capital letter

 

hi Valentino,

You can use the StrComp function and specify vbBinaryCompare, which is 0.
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/strcomp-function

?StrComp("aaa","AAA",0)
 1

if they were equal, 0 would be returned

respectfully,
crystal

MyContacts -- free Access database to organize import people in your life
https://www.msAccessGurus.com/tool/MyContacts.htm

On 10/27/2018 8:47 AM, valentino.avvisati@gmail.com [MS_Access_Professionals] wrote:

Good  morning to all,

I need help for this problem:

 

I take regularly some data from a  database that is case sensitive.

Since some time  original data start to be different just for capital letter and I would like  to avoid to change too much my Access database with which I extract the data. 

I have the following problem that can be summarised in this (in this description I simplified using only two table):

I  downloaded in Access the data  I have 2 tables in  Access where this data is saved.

 

aptbstr01

aptbstr02

 

in each table I have 2 record

Aaa

AAA

When I make a query with the query generator of access as follow to see the record that have the same filed in both table:

 

SELECT aptbstr01.code, aptbstr02.code

FROM aptbstr01 INNER JOIN aptbstr02 ON aptbstr01.code = aptbstr02.code;

 

I get a result of 4 record because for Access is not case sensitive and mainly is considering double the record that has one field that differ only for the capital letter respect another record. 

 

I know that I can use ASC function to transform the value in  the asci character  and after I can join the asci field  but I would like to simplify since join query between the fields that have this issue are more than one and  sometime involving a lot of table connected.

I would like to force access when it is doing the join to compare the asci character of my value so to consider Aaa different from AAA

Is this possible?

Any suggestion how to manage this issue?

 

Thank you

Valentino

 

__._,_.___

---

Posted by: crystal 8 <strive4peace2008@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___