Re: Microsoft ACCESS orderby case insensitive

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

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.
--
Duane Hookom
MS Access MVP


"qyang" <qyang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9A68A989-4489-41DC-96A0-C4F7A3B7BC96@xxxxxxxxxxxxxxxx
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.


"Duane Hookom" wrote:

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

"Van T. Dinh" <VanThien.Dinh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:upOiCjZbGHA.3364@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
HTH
Van T. Dinh
MVP (Access)



"qyang" <qyang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EADCDF7B-A38F-4F72-9A40-153187B42BBB@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: Primary Key not sorted
    ... Are you willing to rely on any "natural" sort of records? ... > though the Company field is the Primary Key but if I ... When I Compact and Repair the database it ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Data sorting for print job
    ... Hence if they pick the second data field then it will sort on this ... 1] is a database the best way or are there alternatives such as ... Access is not bad as a desktop database. ... OTOH, relational database management systems usually are more ...
    (borland.public.delphi.database.ado)
  • Re: WHY
    ... >it would be GROSSLY inefficient to do sorting on 100 different machines ... each of these phones has had to sort the same numbers ... table or an OLAP cube but in an array in memory having just been calculated ... it's way to a database, but it'd be read and used in calculations prior to ...
    (microsoft.public.excel)
  • Re: sorter script [was: Frustrated newbie question]
    ... was from the "perldoc -q sort" as a way of ... most 'elegant' of looking algorithms. ... that bogged the database engine down. ... was due to bad implementation on the db engine. ...
    (perl.beginners)
  • RE: For Duane Hookom
    ... I have been working away and my database is really coming along nicely. ... "Duane Hookom" wrote: ... question then the new question prior to adding possible responses. ... When I add an additional question in the sub-form to any survey there is no ...
    (microsoft.public.access.tablesdbdesign)