Solved

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

Posted on 2011-03-02
311 Views
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
0
Question by:EWHTLC
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 120

Assisted Solution

Rey Obrero (Capricorn1) earned 250 total points
ID: 35017059
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

LVL 11

Accepted Solution

LambertHeenan earned 250 total points
ID: 35018166
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

Author Comment

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

Brilliant response.

Will split the points.
0

Author Closing Comment

ID: 35019676
thanks
0

LVL 11

Expert Comment

ID: 35019912
My pleasure.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
###### Suggested Courses
Course of the Month6 days, 14 hours left to enroll