Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Question Unanswered: Extracting a specific range of data

    Hi hope you can help,

    The examples below are from a customer table, what I'm trying to do rather unsuccessfully at the moment, is I need to extract just the string value between the (( )) double brackets.

    Bramford ((West Bromwich)) becomes West Bromwich
    Nedsworth Mill ((R'ham Stores)) becomes Rham Stores
    Bingfordsley ((Port Talbot Works) Stores becomes Port Talbot Works

    Thanks in advance

    Mark

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You can do this with cunning use of the MID$() and INSTR() functions. Mid allows you to take portions of text, instr lets you find character patterns within text so you will need to combine the two to create your expression. The specific syntax is in Access help

    HTH

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest you write a custom function to do the extract..... putting the vba code inline in a query is going tobe very very messy and a pig todebug.

    first off I'd expect soemthing like
    Code:
    Private Function ExtractLocation(strLocation As String) As String
    'ok so we are looking for a location encapsualted by double brackets "((<mylocation>))"
    Dim StartAt As Integer 'will contain the character position to start our location extract
    Dim EndAt As Integer   'will contain the character position to end our location extract
    StartAt = InStr(1, strLocation, "((")
    EndAt = InStr(1, strLocation, "))")
    If StartAt = 0 Or EndAt = 0 Then 'we haven't found the location temrinators (( or ))
      ExtractLocation = strLocation
    Else
      ExtractLocation = Mid$(strLocation, StartAt + 2, EndAt - 2 - StartAt)
    End If
    End Function
    and called using revisedlocation=extractlocation(<myvariable/control/column>)

    however that would fail as you only have one closing bracket on the Bingfordsley location.

    so it needs to be revised..
    Code:
    Private Function ExtractLocation(strLocation As String) As String
    'ok so we are looking for a location encapsualted by double brackets "((<mylocation>))"
    Dim StartAt As Integer 'will contain the character position to start our location extract
    Dim EndAt As Integer   'will contain the character position to end our location extract
    Dim FoundBrackets As Boolean
    StartAt = InStr(1, strLocation, "((")
    If StartAt = 0 Then 'we didn't find a double opening bracket, is there just the single one?
     StartAt = InStr(1, strLocation, "(")
     If StartAt > 0 Then 'ok so we've found a single bracket
       StartAt = StartAt + 1 'redefine the extract point to be +1 ie we want to start immedaitely after the (
       FoundBrackets = True
      End If
    Else
     StartAt = StartAt + 2 'redefien the extract point to be +2 ie we want to start immedaitely after the second (
     FoundBrackets = True
    End If
    If FoundBrackets = True Then 'ie we found an openign bracket, have we got a closing bracket(s)
      EndAt = InStr(1, strLocation, ")") 'actually we dont care if there are two brackets.. just look for the first closing bracket
      If EndAt = 0 Then 'we haven't found the location terMinator )
        ExtractLocation = strLocation ' so just return the vlaue as supplied, this coudlbe changed to 'unknown location; or anything else
      Else
        ExtractLocation = Trim(Mid$(strLocation, StartAt, EndAt - StartAt)) 'extract the location, then chop off any leading or trailing spaces
      End If
    Else
      ExtractLocation = strLocation ' so just return the vlaue as supplied, this coudlbe changed to 'unknown location; or anything else
    End If
    End Function
    HTH

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I have attached a data base for you that on single run will convert those specemens to

    02BramfordWest Bromwich
    02Nedsworth MillR'ham Stores
    02BingfordsleyPortTalbot Works

    Open the form and at the top left there is a field for Phone Numbers. Trace that back to the table and fill the field with your names. At the top of the form there are three labels....Run Once, Run To Complete and All Records. Clicking All Records will convert every record to the above. Basically all spaces, brackets, dashes are removed. There is also a field about half way down the right column of fields called SearchNum. This if for any wildcards that I had not included. Thus if names have { in them then you enter that and they get dumped.

    You can modify it to stop the 02. I use this for formatting phone numbers and the 02 is an area code in Australia. From memory it is done in the query with a & " " & and an IIF for the state.

    Also incuded is a module which I was kindly provided with by (I will go back to my posts to get the thread) and that is for separating joined names with no spaces on the capital letter. You would need to do that after the "phone fixer" data base converts them. However, you won't get them in one go.

    Note My appreaciation for jmahhaffie for giving me the module to do the split on joined names based on UpperCase characters

    http://www.dbforums.com/showthread.php?t=1625840

    If you check the "phone fixer" what the macro does is set the value of the field with the numbers/names with the result and then continues on. The bottom right field AddSTD is the final result that is fed back to the first field. I would so a similar thing with the capital letter separation.

    Note For the attached data base I pulled the bits from a another data base where I have a lot of this stuff. I tested it and seemed OK. But if a bug, then let me know as I might have missed something
    Attached Files Attached Files
    Last edited by Mike375; 01-10-08 at 08:31.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gosh you are in a generous mood today Mark

    As an extension to the reusablity of your code, why not add two more input params (start delimiter and end delimiter)?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good grief - there must be something in the water today.

    Mike - there is no db attached - did you forget?

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Wow

    I had better put mine back in its box

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by pootle flump
    Good grief - there must be something in the water today.

    Mike - there is no db attached - did you forget?
    Yep. I can not only see it but opened it

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Gosh you are in a generous mood today Mark

    As an extension to the reusablity of your code, why not add two more input params (start delimiter and end delimiter)?
    yup on both counts..

    just had an annoying morning dealing with users who can't be bothered to do things themselves (because there time is oooh so important) and there always that bloke in app support who'll help out and do it for me....... so I though Id dip my toes in here and knock up a quick and dirty function

    well if the truth be told (and that'd be the first with me..... musn't be in touch with my feminine side today) I've just had a mathmatician arguing that the calculations using inbuilt Access statistical functions were wrong because it didn't give the result she expected....... till I pointed out that 0 is not the same as Null. Whereas she knew none of her students could get 0 in a mark and ignored 0's in her test calcs....so deffo Microsoft was wrong not her data.... jeez 30 minutes of rant, 5 minutes of explaining, because she left it to the last minute to do her work..... mind you I suppose I should be grateful that at least she did some testing

    wahaay its 12:30 time for lunch

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    mind you if all you want to do is remove the brackets just use the repalce function!

  11. #11
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1


    Appreciate all your help, got the database Mike. Just about to go for lunch, Ill let ya know how it goes.

    Find and replace no good bud, I need the data within the brackets

    Mark

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by MarkWhyte


    Appreciate all your help, got the database Mike. Just about to go for lunch, Ill let ya know how it goes.

    Find and replace no good bud, I need the data within the brackets

    Mark
    Mark,

    The data base will pull the brackets. You could go into the query (a New York subway would be easier ) and remove the ( and ). However, it would be very easy to add the brackets back. To go from West Bromwich to ((West Bromwich)) will be real easy.

    Mike

  13. #13
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Mark,

    Attached is another DB that you put the names in after they have been through the PhoneFixer Don't forget to put ID or whatever in thee PhoneFixer with the names so you can link back up.

    I have not automated the attached so a some copy/paste will be needed. However, you knock them over in a minute or so.

    After the names have been through the PhoneFixer put them in the attahced DBs table FirstSecond and the field FirstName.

    Then open the query called MarkWhyte. You will see that the second column ([FN] is what you entered minus the last word starting with a capital and the third column [LN] has that last word, thus

    AllanJohnSmith AllanJohn Smith

    You will see there are fields a, b, c, d, e, and f.

    Copy and paste [LN] into [a] and then copy and paste [FN] to [FirstName] and start again.

    The 02 is no problem as it is left behind at the end. In other words "Allan" will be pulled from 02Allan. So at the finish you will have every word in a column and then join 'em as required.

    If I get some time tomorrow I might automate it. I might already have something here that is close. It is just a matter of setting the value of the fields in the appropriate order. Another way would be to make replica calculate fields with the expressions working on the previous answers.
    Attached Files Attached Files

  14. #14
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Well back from my Subway ,

    Cheers Mike for all your help, greatly appreciated

    Mark

  15. #15
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Mark,

    This attachment is same DB as previous but will automate it.

    1) Put names through PhoneFixer

    2) Put the results of the PhoneFixer into DB MarkWhyteVers2 and the field FirstName of the table FirstSecond

    3) Open form MarkWhyte and click the label at the top......Click this Label...and then watch them whiz down the screen.

    It is set to break up at least 6 groups of capitals

    Thus it break up 02TsssHfwwRrwrrwHerwrTsgsfgLrrrtt (as they come form the PhoneFixer) into 02 Tsss Hfww Rrwrrw Herwr Tsgsfg Lrrrtt

    The last Lrrrtt will be in field [a] and Tsgsfg in field [b] etc. The 02 is left in the FirstName field

    If you need to do more than 6 then add fields [g[, [h] as required to table FirstSecond and then alter Macro2 accordingly.

    If you have a lot of records like 10,000 or so it will take a couple of minutes to run down the page. Again, the 02 from the PhoneFixer is no issue as it is left on its own

    Have fun
    Attached Files Attached Files
    Last edited by Mike375; 01-10-08 at 12:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •