select unique record

[yn19832]

I want to select unique country names from a recordset and now my code is

strSELECT = "select distinct country"

The problem is that ASIA and Asia are different, but it only selects one.How can I include both of them?

 

[rainman89]

Search for UCase on the forum, you should get your answer

 

[yn19832]

I am afraid I could not. Could you please give me some clue?

 

[rainman89]

What this will do is make them all caps, in other words. convert all of your countries to UCase[country] then they will be the same
so

"SELECT DISTINCT UCase[country]"

Think thats it

 

[boblarson]

The problem lies in that Access, unlike VB, isn't case sensitive (in most cases) when it comes to strings. You can test that by creating a query in the QBE grid and type the text in with all caps and then try it again in all lower case, and then in mixed case. It should return the same thing each time. I'm not sure how you can get over that hurdle.

 

[Jon K]

Hi,

Try this query, which tests for the ASCII numbers of the second characters in the spelling of the country names:-

SELECT DISTINCT [Country], ASC(Mid([Country],2)) AS ASCNum
FROM [Tablename];
.