Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function to extract specific word (Access 2000)

    Hi All,
    I have a field called 'ConstUnit' that contains records such as the one below

    MOD. 01 - TOPSIDES SMALL BORE PIPING ISOMETRIC

    I want to be able to extract "Mod. 01" and place it in a seperate field called "Module"
    Would someone know the appropriate vb function for it?. As I'm getting alot of errors when I attempt it.
    I've already created the Module in the table and appopriate query.
    Thankyou in advance

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function to extract specific word (Access 2000)

    Do you wish to remove the characters "mod. 01" from the existing string? And do all the records contain the same length string that you want to move? If so then the Left() string function can be used to do what you want to. If you only want to do that for selected records, or you have specific strings you need to search for, you could use the Instr() function but it is a fair bit more complicated and is more effective when used in VBA. If you want to see the details for these functions, open a module in the VBA editor, and then bring up the VBA help.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function to extract specific word (Access 2000)

    Wendell has pointed you in the right direction. Just an additional warning: it may not be a good idea to name a field "Module" since that is also the name of an Access object. Using a name like that may bite you at an unexpected moment.

    The expression for the part to the left of the hyphen would be
    Left([ConstUnit],InStr([ConstUnit]," - ")-1)
    and the expression for the part after the hyphen would be
    Mid([ConstUnit],InStr([ConstUnit]," - ")+3)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function to extract specific word (Access 2000)

    If you will never have more than one hyphen in the field, you could also use the Split function to parse the two parts into an array and then just select the value by index, 0 or 1.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    Hi Hans,
    Ok, I've tried this approach, but I'm getting error messages, and i'm quite lost here... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Public Function GetMod(ConstUnit As String) As String
    Dim iStPos As Integer, iEndPos As Integer
    iStPos = InStr(1, Description, """", vbTextCompare)
    If iStPos = 0 Then
    GetMod = ""
    Else
    iEndPos = iStPos - 1
    Do While iStPos > 0
    If Mid(ConstUnit, iStPos, 1) = " " Then
    iStPos = iStPos + 1
    Exit Do
    End If
    iStPos = iStPos - 1
    Loop
    If iStPos = 0 Then
    GetMod = ""
    Else
    GetMod = Mid(ConstUnit, iStPos, iEndPos - iStPos + 1)
    End If
    End If
    End Function

  6. #6
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    Hi Wendell,
    I guess instr would be the best,
    The records i would need are consistent as you can see below
    MOD. 01 - TOPSIDES SMALL BORE PIPING ISOMETRIC
    MOD. 01 - TOPSIDES SMALL BORE PIPING ISOMETRIC
    MOD. 09 - TOPSIDES SMALL BORE PIPING ISOMETRIC
    MOD. 12 - TOPSIDES VISUAL WORKS
    MOD. 12 - TOPSIDES SMALL BORE PIPING ISOMETRIC

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function to extract specific word (Access 2000)

    What is Description? It is not declared within the function. Apart from that, I don't have the slightest idea what this function has to do with your original question. This function seems to look for a quote within the string, and then extract the last 'word' before the quote.

  8. #8
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    sorry about that, description is supposed to have "constunit" instead. I've attempted to modify a previous function, that works along the line of taking a number before a quote.
    I thought i could modify to suit my purpose, as you can see, I'm quite ignorent when it comes to this. Still in the learning process.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function to extract specific word (Access 2000)

    Have you tried the expression Left([ConstUnit],InStr([ConstUnit]," - ")-1) I proposed in a previous reply? That should get the "mod" part from ConstUnit without having to write a function. Or if you want a function, itr could look like this:

    Public Function GetMod(ConstUnit As String) As String
    GetMod = Left(ConstUnit, InStr(ConstUnit, " - ") - 1)
    End Function

  10. #10
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    Ok, I did try two methods below,,both in a function and I'm getting an "invalid procedure call or argument" error message

    Public Function GetMod(ConstUnit As String) As String
    GetMod = Left(ConstUnit, InStr(ConstUnit, " - ") - 1)
    End Function
    as well as the


    Public Function GetMod(ConstUnit As String) As String
    GetMod = Left([ConstUnit], InStr([ConstUnit], " - ") - 1)

    End Function

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function to extract specific word (Access 2000)

    Use the first version (the square brackets were meant fo direct use in a query). With the Visual Basic Editor open, activate the Immediate window (Ctrl+G). Type (or paste) the following line, then press Enter:

    ? getmod("MOD. 12 - TOPSIDES SMALL BORE PIPING ISOMETRIC")

    Do you get an error message? If so, there must be some other problem, for the function itself is OK.

  12. #12
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    hhhmm, I didnt get an error message at all. It worked perfectly in the immediate window.
    But I think its something to do with the query.
    GetMod([ImportExcel].[F3]) AS Mod
    Although it does look right to me...

  13. #13
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    Hans,
    I wouldnt mind using it in a query, if it will do the job.
    So would I place the below in 'field'?
    GetMod = Left([ConstUnit], InStr([ConstUnit], " - ") - 1)

    Sorry for all these troubles..

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    Close <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    GetMod:Left([ConstUnit], InStr([ConstUnit], " - ") - 1)


    Peter

  15. #15
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to extract specific word (Access 2000)

    Hi Peter,
    It would be great if you can eloborate. Not sure where your answer is supposed to go.
    Thanks

Page 1 of 2 12 LastLast

Posting Permissions

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