Results 1 to 7 of 7
  1. #1
    nathanielban is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    4

    Unhappy Split Numeric Data out of Irregular Text Field

    I have inherited a database at my new job, that is, to say the least, in sorry shape.

    Some background, it's a Access 2000 format database, for our issue tracking software, which was written by idiots.

    Numeric Data is stored as strings etc.

    We have a field named REQUESTOR, on a table TASKS, Which has been used as a dumping ground for not only the requestors name, but in some cases their account number, which is typically a 5-7 digit number. It would be the only numerical data in the field.

    I'd like to write a query that moves or at least copies that number to another field, in this case WO_TEXT6.

    I've been looking around all morning, but the lack of regular formatting is an issue

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    the first thing you need to change is this:
    Quote Originally Posted by nathanielban View Post
    for our issue tracking software, which was written by idiots.
    If a program has helped make money, idiots did not write it. Furthermore, surely the idiot is the person that says this about others.

    The most popular software in the world was written by geniuses that probably don't put too much emphasis on theoretical perfection, and some don't know a thing about it.

    secondly:

    could you update based on what the value is in the old column? maybe not, have you tried it though?

    if not, you'll have to use a recordset I'm sure.

  3. #3
    nathanielban is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    4
    Basically, I have the field, which is in the format of Requestor name #######, or in some cases the number then the name, or in others the name, the number, then some more information. This is obviously less than ideal. I'd be happy to just move all numbers from the request field to the other, but have no idea how to go about doing it. Access is by no means my poison of preference.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You can create a custom function to extract the number portion from a string. The code would go something like this:

    Code:
    Public Function getNumber(mystring As String) As Long
    Dim holdnumeric As String
    Dim i As Long
    For i = 1 To Len(mystring)
        If IsNumeric(Mid(mystring, i, 1)) Then
            holdnumeric = holdnumeric & Mid(mystring, i, 1)
        End If
    Next i
    getNumber = Val(holdnumeric)
    End Function
    You can then use the function in queries, reports, forms etc. I set the function up to return a long number. If the numeric portion falls outside of the field size requirements for a long number you may need to change it to a single or double precision number

    From Access Help:Long Integer(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).


    I've attached a sample database that includes the function. I've also included a query that shows what the function returns.

  5. #5
    nathanielban is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    4

    Will try this now.

    Will try this now.


    Quote Originally Posted by jzwp11 View Post
    You can create a custom function to extract the number portion from a string. The code would go something like this:

    Code:
    Public Function getNumber(mystring As String) As Long
    Dim holdnumeric As String
    Dim i As Long
    For i = 1 To Len(mystring)
        If IsNumeric(Mid(mystring, i, 1)) Then
            holdnumeric = holdnumeric & Mid(mystring, i, 1)
        End If
    Next i
    getNumber = Val(holdnumeric)
    End Function
    You can then use the function in queries, reports, forms etc. I set the function up to return a long number. If the numeric portion falls outside of the field size requirements for a long number you may need to change it to a single or double precision number

    From Access Help:Long Integer(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).


    I've attached a sample database that includes the function. I've also included a query that shows what the function returns.

  6. #6
    nathanielban is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    4
    Thanks jzwp11, that was exactly what I needed.

  7. #7
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome; glad we could help you out.

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

Similar Threads

  1. split text field
    By Zukster in forum Queries
    Replies: 4
    Last Post: 01-11-2011, 10:01 PM
  2. Bring in numeric text field to access from excel
    By Sck in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2010, 04:07 PM
  3. Creating a Flat File with Signed Numeric Data Fields
    By two_smooth in forum Database Design
    Replies: 2
    Last Post: 10-27-2010, 08:31 AM
  4. Split uneven text field
    By swaroop1012 in forum Queries
    Replies: 1
    Last Post: 09-11-2009, 07:42 AM
  5. Split text field into two text fields
    By Grant in forum Access
    Replies: 6
    Last Post: 01-31-2008, 05:52 AM

Tags for this Thread

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