• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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
Asked:
PeterBaileyUk
  • 23
  • 15
  • 6
2 Solutions
 
CluskittCommented:
If IsNumeric(Mid(str, InStr(str, "(") + 1, InStr(str, ")") - 1)) Then
0
 
JezWaltersCommented:
No offense to Cluskitt, but his suggestion doesn't work with your last example.

0
 
JezWaltersCommented:
... and the Length parameter is wrong too
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JezWaltersCommented:
It might be easier to use a Like expression - are the numbers you're testing for only ever 2 or 3 digits long?
0
 
JezWaltersCommented:
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
 
JezWaltersCommented:
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
 
JezWaltersCommented:
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
 
PeterBaileyUkAuthor 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

Open in new window

0
 
JezWaltersCommented:
That function won't give you the right answers anyway ... please try my IsValid() function instead.
0
 
JezWaltersCommented:
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
 
PeterBaileyUkAuthor 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
 
JezWaltersCommented:
Have you tried reopening the database?  What about my sample database?
0
 
PeterBaileyUkAuthor Commented:
just doing both now!
0
 
PeterBaileyUkAuthor 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
 
JezWaltersCommented:
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
 
JezWaltersCommented:
Sorry to bug you, but you still haven't answered my questions in id 35198312 above...
0
 
PeterBaileyUkAuthor Commented:
I did respond,  I said they could occur 35198365
0
 
PeterBaileyUkAuthor 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
 
JezWaltersCommented:
But should these cases pass or fail?
0
 
PeterBaileyUkAuthor Commented:
they should pass sorry! wasnt very clear there! :)

0
 
JezWaltersCommented:
In other words capitalisation is irrelevant?
0
 
PeterBaileyUkAuthor Commented:
yes irrelevant for caps or smallcase
0
 
JezWaltersCommented:
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
 
JezWaltersCommented:
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
 
PeterBaileyUkAuthor 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
 
PeterBaileyUkAuthor 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
 
CluskittCommented:
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
 
JezWaltersCommented:
Cluskitt,

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

0
 
JezWaltersCommented:
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
 
PeterBaileyUkAuthor 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
 
PeterBaileyUkAuthor Commented:
maybe if it strippoed out the (AtoZ) and then used the existing code?
0
 
JezWaltersCommented:
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
 
CluskittCommented:
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
 
PeterBaileyUkAuthor Commented:
?isvalid("xxx (ABC)x xxx(109) xxxxx")
False

should return true as contains (109)
0
 
JezWaltersCommented:
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
 
CluskittCommented:
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
 
JezWaltersCommented:
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
 
JezWaltersCommented:
Cluskitt,

You need "Option Compare Text" too, to force case-insensitive comparisons (see id 35199688 above).
0
 
CluskittCommented:
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
 
JezWaltersCommented:
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
 
CluskittCommented:
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
 
JezWaltersCommented:
I think we got there in the end!  :-)
0
 
PeterBaileyUkAuthor 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
 
PeterBaileyUkAuthor Commented:
hidious problem but very patient experts
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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