MartyConnelly
martyconnelly at shaw.ca
Fri Aug 15 15:13:33 CDT 2003
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
>
>