Possible to ignore case in ACCESS statement?

[Thomson, Martyn EDUC:EX]

I was thinking along the lines of your 3rd suggestion ("Use a view that
upper cases the client name..."), but could find no SQL function to shift
the field to uppercase. My manuals were printed Dec 1991. Tried UPPER() and
it works great!
Thanks for the tip.
Martyn

-----Original Message-----
From: Darren Reely [mailto:darren.reely@latticesemi.com]
Sent: Friday, April 29, 2005 4:28 PM
To: Thomson, Martyn EDUC:EX
Cc: POWERHOUSE NEW (E-mail)
Subject: Re: Possible to ignore case in ACCESS statement?


I can think of three ways to do this.

You could customize the POSTPATH procedure to adjust the client_name to 
begin with an upper case letter. The problem with this is, what if the 
user types "acme c@" but the database entry is "Acme Company".

Use a cursor to upper or lower case both sides of the equation. This 
isn't optimal but is usable. I think you'd need to use a temporary value 
for the prompt field.

Here is some untested/unverified code:

   temporary t_client_name varchar*30 reset at startup
   sql in PR declare myview cursor for &
     select * from client_view &
     where upper(client_name) = :upper(t_client_name)

Use a view that upper cases the client name and then force the same for 
the entry on the view screen. Then just redisplay what you get back. 
Simple to maintain and you don't have to worry about cursor quirks of PH.

Good luck.

Darren

P.S. I don't suggest you do "select *" unless you really want everything 
in a record. Only select what you'll be using, otherwise you'll take a 
performance hit.


Thomson, Martyn EDUC:EX wrote:
>>Hi,
>>I have a simple inquiry screen that retrieves a view of client data by
>>client Id or name. Most but all of the names are stored in title case.
>>
>>file Client_view IN PR PRIMARY transaction query occurs 1 cache ·
>>access via client_id request client_id
>>access via client_name request client_name orderby client_name 
>>
>>Retrieval on client_name is generic so trailing wildcard "@" can be used,
>>but is there any way to ignore case? Currently the user has to remember to
>>capitalize the first letter.
>>
>>Martyn
>>OpenVMS Alpha V7.3-1, OpenVMS Alpha V7.3-1; Powerhouse 7.10G1 
>>
> 
> 

-- 

-----------------------------------
HELP STOP SPAM. STOP USING WINDOWS.
-----------------------------------