Hi John,

Out of curiosity I wrote a similar function using Static variables to
avoid iterating the string every time the function is called. I timed
update queries using the two functions, running them against a table
containing 100,000 random sequences, i.e. 400,000 function calls. The
sequences were of random lengths between 300 and 599 characters.  

Results of three runs of each query suggest that the Static approach is
usefully faster. These are hh:mm:ss:
    Vinson: 00:05:22
    Nurick: 00:02:35
    Vinson: 00:05:20
    Nurick: 00:02:34
    Vinson: 00:05:19
    Nurick: 00:02:37

Here's the function I used:

Public Function AllAtOnce(Sequence As String, Base As String) As Long
 Static S As String
 Static ACount As Long
 Static CCount As Long
 Static GCount As Long
 Static TCount As Long
 Dim j As Long
 
 If Sequence <> S Then 'perform a count
   S = Sequence
   ACount = 0
   CCount = 0
   GCount = 0
   TCount = 0
   
   For j = 1 To Len(S)
     Select Case Mid(S, j, 1)
       Case "A": ACount = ACount + 1
       Case "C": CCount = CCount + 1
       Case "G": GCount = GCount + 1
       Case "T": TCount = TCount + 1
     End Select
   Next
 End If
 
 Select Case Base
   Case "A": AllAtOnce = ACount
   Case "C": AllAtOnce = CCount
   Case "G": AllAtOnce = GCount
   Case "T": AllAtOnce = TCount
 End Select
End Function

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Neat. This query

UPDATE tblMain
SET
 ACount = Len([Sequence])-Len(Replace([Sequence],"A","")),
 CCount = Len([Sequence])-Len(Replace([Sequence],"C","")),
 GCount = Len([Sequence])-Len(Replace([Sequence],"G","")),
 TCount = Len([Sequence])-Len(Replace([Sequence],"T",""))
;

updated the 100,000 records in about 1'34", a minute faster than my
"static" function.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.