Check if string contains numeric characters (1 Viewer)

[supmktg]

Is there an easy way in VBA to loop through a recordset and determine if the text string in a text field includes numeric characters?

I'm trying to do something like this:

Do Until rst.EOF

rst.Edit
If Left(rst!FldText,10) contains any numeric characters Then
rst!FldType = "Mixed"
ElseIF Left(rst!FldText,10) contains "PO" Then
rst!FldType = "PO"
Else
rst!FldType = "Std"
End If
rst.Update

Any help would be greatly appreciated,
Sup

 

[MarkK]

Rather than looping through the recordset you'd probably get way better performance to run a few update queries, something like . . .

With CurrentDb
   .Execute _
      "UPDATE YourTable " & _
      "SET fldType = 'STD'"
   .Execute _
      "UPDATE YourTable " & _
      "SET fldType = 'PO' " & _
      "WHERE Left(fldText, 10) LIKE '*PO*'"
   .Execute _
      "UPDATE YourTable " & _
      "SET fldType = 'Mixed' " & _
      "WHERE Left(fldText, 10) LIKE '*#*'"
End With

. . . or better still, just calculate the field in the query, and don't store it or update it . . .

SELECT fldText,
IIf(Left(fldText, 10) LIKE "*#*", "Mixed", IIf(Left(fldText, 10) LIKE "*PO*", "PO", "STD")) As fldType
FROM YourTable

 

[Galaxiom]

In addition to Lagbolt's solution I thought it worth mentioning about how to use Regular Expressions in Access queries.

REs are very versatile for finding patterns in strings. They can manage much more complex patterns than the sql wildcards.

http://www.access-programmers.co.uk/forums/showthread.php?t=254486