Solved

check if numerals in brackets

Posted on 2011-03-23
278 Views
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
Question by:PeterBaileyUk
[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
• 23
• 15
• 6

LVL 18

Expert Comment

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

LVL 17

Expert Comment

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

0

LVL 17

Expert Comment

ID: 35198115
... and the Length parameter is wrong too
0

LVL 17

Expert Comment

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

LVL 17

Expert Comment

ID: 35198238
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

LVL 17

Expert Comment

ID: 35198297
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

LVL 17

Expert Comment

ID: 35198312
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 Comment

ID: 35198365
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

LVL 17

Expert Comment

ID: 35198411
0

LVL 17

Expert Comment

ID: 35198462
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 Comment

ID: 35198481
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

LVL 17

Expert Comment

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

Author Comment

ID: 35198590
just doing both now!
0

Author Comment

ID: 35198785
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

LVL 17

Expert Comment

ID: 35198881
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

LVL 17

Expert Comment

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

Author Comment

ID: 35198944
I did respond,  I said they could occur 35198365
0

Author Comment

ID: 35198958
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

LVL 17

Expert Comment

ID: 35198994
But should these cases pass or fail?
0

Author Comment

ID: 35199023
they should pass sorry! wasnt very clear there! :)

0

LVL 17

Expert Comment

ID: 35199550
In other words capitalisation is irrelevant?
0

Author Comment

ID: 35199688
yes irrelevant for caps or smallcase
0

LVL 17

Expert Comment

ID: 35199809
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

LVL 17

Expert Comment

ID: 35199874
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 Comment

ID: 35199883
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 Comment

ID: 35199896
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

LVL 18

Expert Comment

ID: 35200168
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

LVL 17

Expert Comment

ID: 35200206
Cluskitt,

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

0

LVL 17

Expert Comment

ID: 35200230
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 Comment

ID: 35200240
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 Comment

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

LVL 17

Expert Comment

ID: 35200265
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

LVL 18

Accepted Solution

Cluskitt earned 250 total points
ID: 35200273
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 Comment

ID: 35200304
?isvalid("xxx (ABC)x xxx(109) xxxxx")
False

should return true as contains (109)
0

LVL 17

Expert Comment

ID: 35200317
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

LVL 18

Expert Comment

ID: 35200357
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

LVL 17

Expert Comment

ID: 35200395
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

LVL 17

Expert Comment

ID: 35200425
Cluskitt,

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

LVL 18

Expert Comment

ID: 35200430
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

LVL 17

Expert Comment

ID: 35200436
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

LVL 18

Expert Comment

ID: 35200445
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

LVL 17

Assisted Solution

JezWalters earned 250 total points
ID: 35200473
I think we got there in the end!  :-)
0

Author Comment

ID: 35200706
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 Closing Comment

ID: 35200811
hidious problem but very patient experts
0

Featured Post

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
Suggested Courses
Course of the Month8 days, 21 hours left to enroll