Microsoft ACCESS orderby case insensitive

[qyang]

I saw Microsoft Access article says that the Access database sort is case
insensitive. However it is not the case, at least for me. I try to sort a
string field using ORDERBY property, but the sort is case sensitive. I really
want it to case insensitive. So I added ucase to the filed name,
ucase(FIELD). Then it prompts me for "Enter Parameter Value", it's not only
annoying, but also does not do any sort at all.

Is there a simple way to sort in case insensitvie?
Thanks

 

[Van T. Dinh]

In my quick tests in Access 2002, the ORDER BY clause sort case-insensitive
and UCase also works fine without param prompt..

Perhaps, you should post the Access version and the SQL String of your query
so that others can check it out.

 

[Duane Hookom]

The only time I have found case-sensitive querying and sorting is when using
tables linked to case-sensitive database systems.

 

[John Vinson]

I saw Microsoft Access article says that the Access database sort is case
insensitive. However it is not the case, at least for me. I try to sort a
string field using ORDERBY property, but the sort is case sensitive. I really
want it to case insensitive. So I added ucase to the filed name,
ucase(FIELD). Then it prompts me for "Enter Parameter Value", it's not only
annoying, but also does not do any sort at all.

Is there a simple way to sort in case insensitvie?
Thanks

Is this data stored in an Access local table? or in SQL/Server or some
other database engine?

In my experience, JET table text (or memo) fields are not case
sensitive, and cannot easily be made case sensitive.

Could you post the SQL view of your query?

John W. Vinson[MVP]

 

[qyang]

Thanks for you both. The Access version I used is Access 2003. I did not use
local Access database tables, instead I used link table which links to a
Oracle database table. The query is:
SELECT * FROM vAgreementProject;
The sort takes place in the form. I dynamically change the FORM.ORDERBY
property based on user selection. I can use such as COMPANYNAME, COMPANYNAME
DESC, but I can not use UCASE(COMPANYNAME), which give me param prompt and
does not do sort.
Thanks again for your time and answer.

 

[qyang]

You're right, I did not use local Access database table, instead I used link
table which links to a Oracle database table. The query is:
SELECT * FROM vAgreementProject;

This may be the reason why I always get a case sensitive sort.

 

[John Vinson]

You're right, I did not use local Access database table, instead I used link
table which links to a Oracle database table. The query is:
SELECT * FROM vAgreementProject;

This may be the reason why I always get a case sensitive sort.

Exactly. Oracle's database engine is doing the sort, and by default it
is case sensitive. It's been a long while since I worked in Oracle,
but if I recall correctly, there is an option to turn off case
sensitivity; or you might need to use a PL-SQL query or view sorted
using one of Oracle's builtin functions.

John W. Vinson[MVP]

 

[Duane Hookom]

I'm not sure why you didn't think to mention ORACLE in your first post. This
is a fairly significant piece of information.

You can change your query to:
SELECT UCASE(COMPANYNAME) AS uCOMPANYNAME, * FROM vAgreementProject;
Then set your FORM.ORDERBY to uCOMPANYNAME.

 

[qyang]

Sorry for that because I've always been working on the Oracle database,
programmed with VBA on Excel, Java and Perl, this is my first Access project
taken from other's responsibility, so I even didn't think of the difference
on the database side.

Your solution may work, but this way will change all the company name to
upper case, user will not be happy with that. I still want to keep the
original company name, but sort them in case insensitive. And also I need to
sort many fields not only one, depends on which field user clicks.
I need to think if there is any better solution.

 

[Duane Hookom]

I didn't expect that you would display the UCASE() column to the user. Just
because you sort by it, doesn't mean it needs to be displayed.