Solved

check if numerals in brackets

Posted on 2011-03-23
44
270 Views
Last Modified: 2012-05-11
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
Comment
Question by:PeterBaileyUk
  • 23
  • 15
  • 6
44 Comments
 
LVL 18

Expert Comment

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

Expert Comment

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

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35198115
... and the Length parameter is wrong too
0
 
LVL 17

Expert Comment

by:JezWalters
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

by:JezWalters
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

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
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

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
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

by:PeterBaileyUk
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

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35198411
That function won't give you the right answers anyway ... please try my IsValid() function instead.
0
 
LVL 17

Expert Comment

by:JezWalters
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

by:PeterBaileyUk
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

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

Author Comment

by:PeterBaileyUk
ID: 35198590
just doing both now!
0
 

Author Comment

by:PeterBaileyUk
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

by:JezWalters
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

Open in new window

0
 
LVL 17

Expert Comment

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

Author Comment

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

Author Comment

by:PeterBaileyUk
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

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

Author Comment

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

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35199550
In other words capitalisation is irrelevant?
0
 

Author Comment

by:PeterBaileyUk
ID: 35199688
yes irrelevant for caps or smallcase
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:JezWalters
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

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
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

by:PeterBaileyUk
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

by:PeterBaileyUk
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

by:Cluskitt
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

by:JezWalters
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

by:JezWalters
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

Open in new window

0
 

Author Comment

by:PeterBaileyUk
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

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

Expert Comment

by:JezWalters
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

by:
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

Open in new window

0
 

Author Comment

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

should return true as contains (109)
0
 
LVL 17

Expert Comment

by:JezWalters
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

by:Cluskitt
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

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
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

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35200425
Cluskitt,

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

Expert Comment

by:Cluskitt
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

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
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

by:Cluskitt
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

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

Author Comment

by:PeterBaileyUk
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

by:PeterBaileyUk
ID: 35200811
hidious problem but very patient experts
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now