Routine to identify 67 character string

[macca]

My problem is that I need a VBA routine to check the 67 character DNA string and return a Process Name in a field called Process. I do not want use Excel as the number of record often exceeds the Excel row limit

ContactID DNA
4479850 0000000000000000000000000000000000000000000000000000000000000000001
4479851 0000000000000000000000000000000000000000000000000000000000000000001
4479852 0000000000000000000010100100000000000000000000000000000000000000001
4479853 1001000000000000000000000000000000000000000000000000000000000000001
4479855 0000000000000000000000000000000000000000000000000000000110000000001
4479857 0000000000000000000010000100000000000000000000000000000000000000001
4479859 1100000000000000000000000000000000000000000000000000000000000000001
4479862 0000000000000000000010100100000000000000000000000000000000000000001
4479863 0000000000000000000000000000000000000000000000000000000000000000001
4479865 0000000000000000000000000000000000000000000000000000000000001000000
4479866 0000000000000000000000000000000000000000000000000000000000000000001
4479871 0000000000000000000000000000000000000000000000000000000000000000001
4479873 0000000000000000001000000000000000000000000000000000001000000000001

I had been looking at Excel using a routine similar to the following, but I must admit I am new to Access VBA

Public Sub rnatest()
    Dim RNArng As Range, cl As Range
    Dim rna As String
    Dim reason As String
     
    Set RNArng = Range("b2", Range("b65536").End(xlUp))

For Each cl In RNArng
    cl.Select
    rna = ActiveCell.Value
    If rna >= "1101000000000000000000000000000000000000000000000000000000001110000" Then
        reason = "Comp resolved"
       'places reason in active cell
        'Debug.Print reason
    ElseIf rna >= "1100000000000000000000000000000000000000000000000000000000000000000" Then
        reason = "Comp resolved"
        'Debug.Print reason
    ElseIf rna >= "1010000000000000000000000000000000000000000000000000000000000000000" Then
        reason = "Comp unresolved"
        'Debug.Print reason
    ElseIf rna >= "1001000000000000000000000000000000000000000000000000000000000000000" Then
        reason = "comp view"
        'Debug.Print reason
    ElseIf rna >= "1000000000000000000000000000000000000000000000000000000000000000000" Then
        reason = "comp no action"
       ' Debug.Print reason
    Else
        reason = "msa"
        'Debug.Print reason
         ActiveCell.Value = reason
    End If
Next cl
End Sub

 

[macca]

Please note there is no gap in the 67 character string, it is just the way it appeared on the thread submission form.

A little bit more information, because of the 67 character string there are more than 3.65E+94 permutations, so a look up table is not an option

What I will look to achieve is that if the string is between a range of values then I can define the process name

 

[Bat17]

You should just be able to turn that into a custom function and use it in a query to fill in the values

Function rnatest(rna as string) as string

    If rna >= "11010000000000000000000000000000000000000000000000  00000000001110000" Then
        reason = "Comp resolved"
       'places reason in active cell
        'Debug.Print reason
    ElseIf rna >= "11000000000000000000000000000000000000000000000000  00000000000000000" Then
        reason = "Comp resolved"
        'Debug.Print reason
    ElseIf rna >= "10100000000000000000000000000000000000000000000000  00000000000000000" Then
        reason = "Comp unresolved"
        'Debug.Print reason
    ElseIf rna >= "10010000000000000000000000000000000000000000000000  00000000000000000" Then
        reason = "comp view"
        'Debug.Print reason
    ElseIf rna >= "10000000000000000000000000000000000000000000000000  00000000000000000" Then
        reason = "comp no action"
       ' Debug.Print reason
    Else
        reason = "msa"
    End If
rnatest = reason
End function

HTH

Peter

 

[RuralGuy]

In case you can use it, here's some code to convert binary to decimal thanks to Terry Kreft a few years back along with some modifications I made to handle that big a number.

Function BinaryToDec(ByVal Binary As String) As Variant
'*******************************************
'Name:      BinaryToDec (Function)
'Purpose:   Converts the string representation _
            of a binary number to a decimal number
'Author:    Terry Kreft
'Date:      July 22, 1999, 11:57:15 PM
'Called by: Any
'Calls:     None
'Inputs:    Binary - string representation _
            of a binary number
'Output:    Converted Number
'*******************************************

  Dim intLen As Integer
  Dim intx As Integer
  Dim DecRet As Variant
  Binary = Trim(Binary)
  intLen = Len(Binary)
  For intx = intLen - 1 To 0 Step -1
    DecRet = DecRet + CDec(Mid(Binary, intLen - intx, 1)) * (2 ^ intx)
  Next
  BinaryToDec = DecRet
End Function

It returns a Decimal number which may work out better for you when doing range comparisons.