<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2726.2500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><STRONG>Rocky,</STRONG></DIV>
<DIV> </DIV>
<DIV> As far as I know, SQL by itself is not going to honor
the 'Option Compare Binary' clause even when it is specified in the
Modules.</DIV>
<DIV> However, if a particular function has provision for an
argument forcing binary comparison (e.g Instr() or StrComp()), and such
function is used in SQL (duly including the compare argument even though
optional), desired results could be achieved.</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV><STRONG>A.D.Tejpal</STRONG></DIV>
<DIV><STRONG>--------------</STRONG></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=bchacc@san.rr.com href="mailto:bchacc@san.rr.com">Rocky Smolin -
Beach Access Software</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">Access Developers discussion and
problem solving</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Friday, August 15, 2003 22:37</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] Option Compare
Binary Problem</DIV>
<DIV><BR></DIV>
<DIV><FONT face=Arial size=2>I think it would require even more
gyrations. Access very nicely sorts the ~ to the top of a list.
Client wanted it at the bottom. So everywhere I have to deal with the ~
I have to work around Access's default methods.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The client just told me to abandon the ~ approach
and go with a lower case z prefix. It's for defining a location where
the default location is 'No Building' and 'No Room', where the user hadn't
selected a location. So prefixing with a _ or a ~ sorted the 'No'
choices to the top of the list. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Using z will sort to the bottom where he wants
it. So now all I have to do it go in and undo all that tilde
stuff. So problem not solved, but abandoned.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Still would like to know how to solve it but it's
not a big priority.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=harkins@iglou.com href="mailto:harkins@iglou.com">Susan Harkins</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">Access Developers discussion and
problem solving</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Friday, August 15, 2003 9:34
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] Option Compare
Binary Problem</DIV>
<DIV><BR></DIV>
<DIV><FONT face=Arial size=2>Can you assign ~ to a constant? </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV><BR> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><FONT face=Arial size=2>Dear List:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I'm using Option Compare Binary in a module
to get the code to recognize that the tilde character "~" is greater than
any alphanumeric character. (Using Option Compare Database it sorts
the tilde up front.)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>However when I assemble and run a SQL
statement that has fld >= "0" and fld <= "~" it return zero
records. If I switch it around so that it says fld >= "~" and fld
<= "0" the right number of records is returned. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I found this out when I took the SQL and
pasted it into a query and tested the criterion both ways. So
apparently the Option Compare Binary doesn't extend to string comparisons
when executing a SQL statement (reasonable).</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Is there a way to get the same Compare effect
in a SQL statement?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thanks in advance to all and
regards,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky Smolin<BR>Beach Access
Software</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>