[AccessD] Option Compare Binary Problem

Rocky,

    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.
    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.

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Rocky Smolin - Beach Access Software 
  To: Access Developers discussion and problem solving 
  Sent: Friday, August 15, 2003 22:37
  Subject: Re: [AccessD] Option Compare Binary Problem


  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.

  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. 

  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.

  Still would like to know how to solve it but it's not a big priority.

  Rocky

    ----- Original Message ----- 
    From: Susan Harkins 
    To: Access Developers discussion and problem solving 
    Sent: Friday, August 15, 2003 9:34 AM
    Subject: Re: [AccessD] Option Compare Binary Problem


    Can you assign ~ to a constant? 

    Susan H. 

     
      Dear List:

      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.)

      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.  

      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).

      Is there a way to get the same Compare effect in a SQL statement?

      Thanks in advance to all and regards,

      Rocky Smolin
      Beach Access Software
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030816/83ef29b0/attachment-0001.html>