• Status: Solved
• Priority: Medium
• Security: Public
• Views: 285

# check if numerals in brackets

str "xxx xxxxxx xxx(109) xxxxx" = pass
str "xxx xxxxxx xxx( 109 ) xxxxx" = pass
str "xxx xxxxxx xxx(10) xxxxx" = pass
str "xxx xxxxxx xxx(109bhp) xxxxx" = pass

I would like a function in vba that tells me if anywhere in the string resides an opening and closing bracket with only numerals in between with the exception of word bhp.

so xxx (lc) would fail the test

0
PeterBaileyUk
• 23
• 15
• 6
2 Solutions

Commented:
If IsNumeric(Mid(str, InStr(str, "(") + 1, InStr(str, ")") - 1)) Then
0

Commented:
No offense to Cluskitt, but his suggestion doesn't work with your last example.

0

Commented:
... and the Length parameter is wrong too
0

Commented:
It might be easier to use a Like expression - are the numbers you're testing for only ever 2 or 3 digits long?
0

Commented:
This should work in all the cases you've cited:
``````If Trim(Mid(strYourString, InStr(strYourString, "(") + 1, InStr(strYourString, ")") - InStr(strYourString, "(") - 1)) Like "#*" Then
``````
0

Commented:
If you need to test strictly for bracketed numerals (and spaces), which may also be followed by the string "bhp", then this should do the trick:
``````Option Explicit
Option Compare Database

Public Function IsValid(pstrString As String) As Boolean

Dim strSubstring As String

strSubstring = Trim(Mid(pstrString, InStr(pstrString, "(") + 1, InStr(pstrString, ")") - InStr(pstrString, "(") - 1))
IsValid = strSubstring Like "#" Or _
strSubstring Like "#bhp" Or _
strSubstring Like "##" Or _
strSubstring Like "##bhp" Or _
strSubstring Like "###" Or _
strSubstring Like "###bhp"

End Function
``````
0

Commented:
Can the following strings occur, and if so should they pass or fail?

xxx (ABC)x xxx(109) xxxxx
xxx xxxxxx xxx(109BHP) xxxxx
0

Author Commented:
yes they could occurr

i have an access error invalid procedure call

am calling the function in the access query grid:

Expr3: containsbhp([abicodesPrevious].[MODEL_DESCRIPTION])
``````Function containsbhp(strIn As String) As Boolean
If Trim(Mid(strIn, InStr(strIn, "(") + 1, InStr(strIn, ")") - InStr(strIn, "(") - 1)) Like "#*" Then
containsbhp = True

Else
containsbhp = False

End If

End Function
``````
0

Commented:
That function won't give you the right answers anyway ... please try my IsValid() function instead.
0

Commented:
You might need to close and then reopen the database to get Access to recognise the new function.

Anyway, here's a sample database, showing it all working:
Q26905763.mdb
0

Author Commented:
ok hadnt got that post at the time but still getting invalid procedure call:

strSubstring = Trim(Mid(pstrString, InStr(pstrString, "(") + 1, InStr(pstrString, ")") - InStr(pstrString, "(") - 1))
0

Commented:
Have you tried reopening the database?  What about my sample database?
0

Author Commented:
just doing both now!
0

Author Commented:
well your db works so it must be the data ok found it some strings do not contain the string (123)

ex

?IsValid("Q5 SE")

it is these ones that fail the call
0

Commented:
Ah yes ... this should fix that problem:
``````Option Explicit
Option Compare Database

Public Function IsValid(pstrString As String) As Boolean

Dim lngClosePosn As Long
Dim lngOpenPosn As Long
Dim strSubstring As String

lngOpenPosn = InStr(pstrString, "(")
If lngOpenPosn Then
lngClosePosn = InStr(lngOpenPosn, pstrString, ")")
If lngClosePosn Then
strSubstring = Trim(Mid(pstrString, lngOpenPosn + 1, lngClosePosn - lngOpenPosn - 1))
IsValid = strSubstring Like "#" Or _
strSubstring Like "#bhp" Or _
strSubstring Like "##" Or _
strSubstring Like "##bhp" Or _
strSubstring Like "###" Or _
strSubstring Like "###bhp"
End If
End If

End Function
``````
0

Commented:
Sorry to bug you, but you still haven't answered my questions in id 35198312 above...
0

Author Commented:
I did respond,  I said they could occur 35198365
0

Author Commented:
Ok its not falling over now, just a question of this scenario:

xxx (ABC)x xxx(109) xxxxx
xxx xxxxxx xxx(109BHP) xxxxx

yes this could occur
0

Commented:
But should these cases pass or fail?
0

Author Commented:
they should pass sorry! wasnt very clear there! :)

0

Commented:
In other words capitalisation is irrelevant?
0

Author Commented:
yes irrelevant for caps or smallcase
0

Commented:
OK ... making the function case insensitive is a simple matter of add the following statement at the top of the module that contains the function:
``````Option Compare Text
``````
0

Commented:
However, you need to clarify the requirements for the other case ... Which of the following cases should pass?

xxx (109)x xxx(ABC) xxxxx
xxx (ABC)x xxx(109) xxxxx
xxx ((109) xxx(ABC) xxxxx
xxx (109xx xxx(109) xxxxx
xxx )ABC(x xxx(109) xxxxx
0

Author Commented:
ok done that I had option compare database

not sure if that will impact elsewhere.

is just this and itll be complete

isvalid("xxx (ABC)x xxx(109) xxxxx")
0

Author Commented:
xxx (109)x xxx(ABC) xxxxx = true
xxx (ABC)x xxx(109) xxxxx= true
xxx ((109) xxx(ABC) xxxxx= true
xxx (109xx xxx(109) xxxxx = true
xxx )ABC(x xxx(109) xxxxx = true

0

Commented:
Sorry, been away for some time, only now can I reply.

Yes, my initial suggestion was flawed. However, I do have a suggestion for you, JezWalters, which might make your work easier. Instead of cutting a string between ( and ), why not just apply the like to the whole string:
... LIKE "%(###)%" OR etc...

Don't need any points, just trying to help
0

Commented:
Cluskitt,

That's the kind of idea I've already used ... but that doesn't cope with the cases PeterBaileyUK just posted.  :-(

0

Commented:
Right ... this should do what you're after:
``````Option Explicit      ' Force explicit variable declaration
Option Compare Text  ' Perform case-insensitive comparisons

Private Enum estState
GET_OPENBRACKET = 1
GET_CLOSEBRACKET = 2
End Enum

Public Function IsValid(pstrString As String) As Boolean

' Declare variables
Dim lngCharacter As Long
Dim staState As estState
Dim strCharacter As String
Dim strSubstring As String

' Process string
staState = GET_OPENBRACKET
For lngCharacter = 1 To Len(pstrString)
strCharacter = Mid(pstrString, lngCharacter, 1)
Select Case staState
Case GET_OPENBRACKET
If strCharacter = "(" Then
strSubstring = ""
staState = GET_CLOSEBRACKET
End If
Case GET_CLOSEBRACKET
If strCharacter = "(" Then
strSubstring = ""
ElseIf strCharacter = ")" Then
strSubstring = Trim(strSubstring)
IsValid = strSubstring Like "#" Or _
strSubstring Like "#bhp" Or _
strSubstring Like "##" Or _
strSubstring Like "##bhp" Or _
strSubstring Like "###" Or _
strSubstring Like "###bhp"
If IsValid Then
Exit For
End If
staState = GET_OPENBRACKET
Else
strSubstring = strSubstring & strCharacter
End If
End Select
Next

End Function
``````
0

Author Commented:
it may be that it copes with 97 % of whats thrown at it, i just wont know yet until i throw more data at it
0

Author Commented:
maybe if it strippoed out the (AtoZ) and then used the existing code?
0

Commented:
Are you referring to the code I just posted, when you say that it copes 97% of the time?  If so, where does it fail?
0

Commented:
Not quite. You were cutting a substring. For example, in the string "xxx (ABC)x xxx(109) xxxxx" you were cutting a substring which would return "ABC". This, when compared to the filters "###" etc, would fail.
However, if you apply the filters for the whole string, allowing for ( and ), will work. Something like:

``````Option Explicit
Option Compare Database

Public Function IsValid(pstrString As String) As Boolean

Dim lngClosePosn As Long
Dim lngOpenPosn As Long
Dim strSubstring As String

lngOpenPosn = InStr(pstrString, "(")
If lngOpenPosn Then
lngClosePosn = InStr(lngOpenPosn, pstrString, ")")
If lngClosePosn Then
IsValid = pstrString Like "%(#)%" Or _
pstrString Like "%(#bhp)%" Or _
pstrString Like "%(##)%" Or _
pstrString Like "%(##bhp)%" Or _
pstrString Like "%(###)%" Or _
pstrString Like "%(###bhp)%"
End If
End If

End Function
``````
0

Author Commented:
?isvalid("xxx (ABC)x xxx(109) xxxxx")
False

should return true as contains (109)
0

Commented:
Cluskitt,

You need to use * instead of %, but even then your approach wont cope with a bracketted number which has an arbitrary number of spaces before or after it
0

Commented:
Yes, I'm afraid I'm a bit tired and didn't remember the * instead of % (working the last few hours with SQL Server didn't help either).

And yes, it won't work with spaces inside the parenthesis. For it to work with spaces, just change to this:

``````Option Explicit
Option Compare Database

Public Function IsValid(pstrString As String) As Boolean

Dim lngClosePosn As Long
Dim lngOpenPosn As Long
Dim strSubstring As String

lngOpenPosn = InStr(pstrString, "(")
If lngOpenPosn Then
lngClosePosn = InStr(lngOpenPosn, pstrString, ")")
If lngClosePosn Then
tempStr = Replace(pstrString, " ", "")
IsValid = tempStr Like "*(#)*" Or _
tempStr Like "*(#bhp)*" Or _
tempStr Like "*(##)*" Or _
tempStr Like "*(##bhp)*" Or _
tempStr Like "*(###)*" Or _
tempStr Like "*(###bhp)*"
End If
End If

End Function
``````
0

Commented:
Actually, that can be simplified to just:
``````Option Explicit      ' Force explicit variable declaration
Option Compare Text  ' Perform case-insensitive comparisons

Public Function IsValid(pstrString As String) As Boolean

pstrString = Replace(pstrString, " ", "")
IsValid = pstrString Like "*(#)*" Or _
pstrString Like "*(#bhp)*" Or _
pstrString Like "*(##)*" Or _
pstrString Like "*(##bhp)*" Or _
pstrString Like "*(###)*" Or _
pstrString Like "*(###bhp)*"

End Function
``````
0

Commented:
Cluskitt,

You need "Option Compare Text" too, to force case-insensitive comparisons (see id 35199688 above).
0

Commented:
Yes, I was just adapting from yours. It might be said that that one will take a bit longer if there are millions of records, due to checking all strings regardless of having parenthesis or not, though it won't be that much longer anyway (unless they're LOTS of millions). You could add a simple check anyway:

``````Option Explicit      ' Force explicit variable declaration
Option Compare Text  ' Perform case-insensitive comparisons

Public Function IsValid(pstrString As String) As Boolean

If InStr(pstrString, "(") And InStr(pstrString, ")") Then
pstrString = Replace(pstrString, " ", "")
IsValid = pstrString Like "*(#)*" Or _
pstrString Like "*(#bhp)*" Or _
pstrString Like "*(##)*" Or _
pstrString Like "*(##bhp)*" Or _
pstrString Like "*(###)*" Or _
pstrString Like "*(###bhp)*"
End If

End Function
``````
0

Commented:
Bear in mind that the code I posted in id 35200395 above only copes with 1-digit, 2-digit or 3-digit positive integers - is this what you want?
0

Commented:
Actually, I was just copying your code. Though it was the first one. My intention was just to help you reach a solution and didn't really pay attention to those things. Once again, I don't need the points, just glad to help :)
0

Commented:
I think we got there in the end!  :-)
0

Author Commented:
yes it was a hidious 1, u ok to share the points? i am just about to post another question but is much simpler.
0

Author Commented:
hidious problem but very patient experts
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.

## Featured Post

• 23
• 15
• 6
Tackle projects and never again get stuck behind a technical roadblock.