Results 1 to 9 of 9
  1. #1
    FrankBone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    4

    Need to pull number from within text string

    The following is a small example of a field in series of records. I need the number from within the 2nd and 3rd dash. Please help. Thanks.



    15-1296-2-468-1413
    15-89440-4-152-779
    15-128441-5-468-1462

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is it always a one digit number? If not, how high can it go?

    If it is always one digit, this will work (where "MyField" is the name of your field):
    Code:
    MyNumber: Mid([MyField],InStr(InStr([MyField],"-")+1,[MyField],"-")+1,1)

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I might create a custom function using the Split() function. It would take the string as an input parameter, and probably also a number representing which position was desired. That would let you return any of the values within the string, which you may need in other places.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I am personally a fan of UDFs, especially if you are not adverse to having VBA in your database, and it isn't a fairly straightforward/simple calculation. So if your number may be more than one digit, I think that tips the scales even further towards using a UDF, like Paul suggested.

    A few advantages to UDFs:
    1. I think they are easier to read/maintain, especially if you start getting into some complex functions.
    2. If you have to use this in more than one spot, if you ever need to change anything, you only have to change the UDF code, instead of changing all the different places you have the calculation.

  5. #5
    FrankBone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    4
    It can be a number between 4 and 7 digits.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I obviously agree.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    FrankBone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    4
    I think a UDF would be the best way to go, but I am so new to using formulas in access. Can you offer a suggestion for this? Thanks.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If it will be 4 to 7 digits, why do examples show only 1 digit after the second dash?

    Will there be any leading zeros? Would they have to be retained?

    A UDF would be great but for an immediate solution, try:

    Val(Mid([MyField],InStr(InStr([MyField],"-")+1,[MyField],"-")+1))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-29-2015, 10:21 AM
  2. Replies: 2
    Last Post: 04-05-2015, 06:06 PM
  3. Replies: 6
    Last Post: 09-10-2013, 08:37 AM
  4. Mid Text String's Number Bump by One
    By CementCarver in forum Programming
    Replies: 2
    Last Post: 04-15-2013, 10:27 AM
  5. Breaking the string into Text / Number
    By Amerigo in forum Queries
    Replies: 15
    Last Post: 05-20-2011, 03:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums