case sensitive sql queries

[senthilkbs]

i have a table(employee) with a field(ename), i want to get list of employees whose name starts with the letter 'a' and not 'A', how can i perform this.

eg:

ambrose
antony
Abraham
amar
Anand

the query must return only the names (ambrose,antony,amar) and not (Abraham,Anand).

try to explain with sample sql query.

thanks for your coordination.

[DerFarm]

You can set the comparision somewhere in Access. The default
(at least for the US) is that UpperCase=LowerCase. I don't know
where the setting is, sorry, I looked but couldn't find.

In module code and VB you set the comparisions at the head of
the module. From the MSDN Help files:

If used, the Option Compare statement must appear in amodule
before anyprocedures.

The Option Compare statement specifies thestring comparison
method (Binary, Text, or Database) for a module. If a module
doesn't include an Option Compare statement, the default text
comparison method is Binary.

Option Compare Binary results in string comparisons based on
asort order derived from the internal binary representations of
the characters. In Microsoft Windows, sort order is determined by
the code page. A typical binary sort order is shown in the
following example:

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

Option Compare Text results in string comparisons based on a
case-insensitive text sort order determined by your
system'slocale. When the same characters are sorted using
Option Compare Text, the following text sort order is produced:


(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

Option Compare Database can only be used within Microsoft
Access. This results in string comparisons based on the sort order
determined by the locale ID of the database where the string
comparisons occur.



HTH

[senthilkbs]

thanks for your interest in responding

This will work for visual basic, but this does not support in asp files, how to obtain this is asp file