# Access 2003 chaeck field to see if it contains a certain pattern

Hi There,

I have a 12 digit field.
I want to be able to check if anywhere in that string it contains 4 consecutive non numeric characters.

eg

1BLT010001   No
2POLK1000    Yes
POL1000        NO
POLK9999      yes

Any Ideas
###### Who is Participating?

Commented:
Here is an alternative function that checks for any desired number of alpha characters in a row...

``````function strNStringAlpha(strInput, nConsecutive) as string
' Checks it there is any string of nConsecutive Alpha characters
' (A-Z, a-z) and returns the string if found
dim nLen as Long
dim n as Long
dim c as String
dim strResult as string
dim nAlpha
nLen = Len(strInput)
For n = 1 to nLen
c = Ucase(mid(strInput,n,1))
If c >="A" and c <= "Z" then
nAlpha = nAlpha + 1
strResult = strResult & mid(strInput,n,1)
if nAlpha = nConsecutive then
Exit For
End If
Else
nAlpha = 0
strResult = ""
End If
Next N
strNStringAlpha = strResult
End Function
``````
0

Commented:
you will need a function to do that, place this codes in a regular module
is there a possibility to have more than 4 consecutive Alpha characters

function count4Alpha(str) as boolean
count4Alpha=false
if len(str) < 4 then count4Alpha=False: exit function

dim j, strA as string, xCnt as integer
strA="ABCDEFGHIJKLMNOPQRSTUVWXYZ"
for j=1 to len(str)
if  instr(strA,mid(str,j,1))>0 then
xCnt=xcnt +1
if xcnt>=4 then
count4Alpha=true
exit function
end if
else
xCnt=0
end if
next

end function

you can use the function in a query like this

select [field], count4Alpha([field])
fro tableName
0

DeveloperAuthor Commented:
Both Solutions worked a treat.
2nd one slightly better in that it looked for 4 consecutives.

Brilliant response.

Will split the points.
0

DeveloperAuthor Commented:
thanks
0

Commented:
My pleasure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.