[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



----------------------------------------------------------------------------


    _______________________________________________
    AccessD mailing list
    AccessD at databaseadvisors.com
    http://databaseadvisors.com/mailman/listinfo/accessd
    Website: http://www.databaseadvisors.com



------------------------------------------------------------------------------


  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030815/ce6afbf7/attachment-0001.html>