Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Fri Aug 15 17:50:22 CDT 2003
Marty:
Beautiful. And if $ were no object, I'd sprinkle it through the whole app.
In this case simpler is better (and cheaper). But the code goes in my
library.
Thanks and regards,
Rocky
----- Original Message -----
From: "MartyConnelly" <martyconnelly at shaw.ca>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Friday, August 15, 2003 1:13 PM
Subject: Re: [AccessD] Option Compare Binary Problem
> One approach might be to use some variation of a hash function that
creates
> a new index field that is a hash of the alphnumeric characters say
> "1.0.1" that
> appears as a double or series of fields converted to doubles. This
> method below
> of hashing is called Base 37 Hash. Numerical Methods text books might
> have documentation.
> By changing the decimal number of the character in code you can alter
> the sort order.
> Say if you want to make A and Z equivalent in the sort order instead of
> z character being
> assigned decimal 37 force it to 11 in code.
> I wrote this originally to sort on a field that was old Radio Tube
> numbers that looked like 2AAW32 2ABW320 2AaW3223 etc and avoid upper
> lower case ordering. You are essentially assigning your own collation
> sequence.
>
> Option Compare Database
> Option Explicit
> Const hashFactor As Integer = 37
> Const hashLength As Integer = 9
>
> Function HashString(strHash As String) As Double
> ' Create Hash string for indexing using
> 'Base 37 Hash Value
> ' Convert
> ' spaces punctuation odd chars = 0
> ' numeric = 0 - 9 1-10
> ' alpha chars a-z A-Z 11-37
> ' only use lower case
> 'such that string "Ab-12" =
> ' A b - 1 2
> ' (11*37^4) + (12*37^3) + (0*37^2) + (2*37^1) + (3*37^0)
> 'The Hash Length is 9 so it fits a double without precision loss
>
> Dim iStrLen As Integer
> Dim decAsc As Double
> Dim i As Integer
> Dim strPad As Integer
> Dim strToHash As String
> HashString = 0
>
> ' convert to all lower case
> strToHash = UCase(strHash)
> iStrLen = Len(strToHash)
>
> 'pad out string to 9 chars with blanks
> If iStrLen < hashLength Then
> For strPad = (iStrLen + 1) To hashLength
> strToHash = strToHash & " "
> Next
> Else
> ' or just grab first nine chars of string
> If iStrLen > hashLength Then
> strToHash = Left(strToHash, hashLength)
> End If
> End If
>
> For i = 1 To hashLength
> decAsc = Asc(Right(strToHash, i))
> 'convert all odd Ascii character values and punctuation to 0
> If (decAsc < 48) Or (decAsc >= 58) And (decAsc <= 64) _
> Or (decAsc > 91) Then
> decAsc = 0
> Else
> 'numbers
> If (decAsc >= 48) And (decAsc <= 57) Then
> decAsc = decAsc - 47
> Else
> 'letters
> If (decAsc >= 65) And (decAsc <= 91) Then
> decAsc = decAsc - 54
> ' 54 not 64 as want to start "A" as 11
> End If
> End If
> End If
>
> HashString = HashString + (decAsc * hashFactor ^ (i - 1))
> Next
>
> End Function
>
> Function HashUpdateHierarchLegalNumber()
> 'Update Method Example
> 'This example demonstrates the Update method in conjunction with Edit
> method. Updates the hash field from original index field in the original
> table.
>
>
> Dim dbs As Database
> Dim rst As Recordset
> Dim String1 As String
> Dim String2 As String
> ' Set dbs = OpenDatabase("c:\access2\hashing\HashStringA.mdb")
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecordset("LegalNumbering")
>
> With rst
> Do While Not .EOF
> .Edit
> String1 = Left(![LegalNumber], 9)
> String2 = Mid(![TubeNumbers], 10, 9)'chunk out further
> 'String2 = Right(![TubeNumbers], 9) 'if number longer than 9
digits
> !HashTitle1 = HashString(String1)
> !HashTitle2 = HashString(String2)
>
> .Update
> .MoveNext
> Loop
> End With
> rst.Close
> dbs.Close
>
> End Function
> Rocky Smolin - Beach Access Software wrote:
>
> > 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 <mailto:harkins at iglou.com>
> > To: Access Developers discussion and problem solving
> > <mailto:accessd at databaseadvisors.com>
> > 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
> >
> >------------------------------------------------------------------------
> >
> >_______________________________________________
> >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
>