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.


1BLT010001   No
2POLK1000    Yes
POL1000        NO
POLK9999      yes

Any Ideas
Who is Participating?
LambertHeenanConnect With a Mentor 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
			nAlpha = 0
			strResult = ""
		End If
	Next N
	strNStringAlpha = strResult
End Function

Open in new window

Rey Obrero (Capricorn1)Connect With a Mentor 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
if len(str) < 4 then count4Alpha=False: exit function

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

end function

you can use the function in a query like this

select [field], count4Alpha([field])
fro tableName
EWHTLCAuthor Commented:
Both Solutions worked a treat.
2nd one slightly better in that it looked for 4 consecutives.

Brilliant response.

Thanks you for your time

Will split the points.
EWHTLCAuthor Commented:
My pleasure.
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.

All Courses

From novice to tech pro — start learning today.