1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Parsing text field in Access 2007

Discussion in 'Business Applications' started by EverettMiller, Aug 5, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. EverettMiller

    EverettMiller Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    7
    My Access 2007 database contains records with a text field of up to 255 characters. these records are being imported into a system that allows up to 39 characters for this data. I'm seeking help to create a query(queries) or macro(s) that will create additional records with each one containing the next 39 characters in the text field. For example, a record contains 125 characters in its text field. I need to end up with 4 records such that record #1 contains the first 39 characters, record #2 contains the next 39 characters, and so forth.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    EverettMiller, welcome to the forum.
    what you want to do can be done using a Query. What is the name of the Text Field?
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    I don't know much about Access but I wrote a vbs function once that did just that
    Code:
    Function WrapText(myText as String, myLen as Integer) as String
    If myLen > len(myText) then exit Function
    Dim x as integer
    for x = 1 to len(MyText) step myLen
      WrapText = mid(MyText,x,myLen)
    next x
    End Function
    
    You'll have to write code that appends new records while WrapText <> ""

    I hope this puts you in the right track
     
  4. EverettMiller

    EverettMiller Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    7
    Hi OBP! Thanks for the reply. The name of the text field is COMMENT
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    Ok, you need a query with the Table that has Comment in it as it's Record Source.
    In a blank column header enter
    1: left([Comment], 29)
    that will give you a field with the first 29 characters.
    in the next column heading enter
    2: IIf(Len([Comment])>=58,Mid([Comment],30,29),Mid([Comment],30,Len([Comment])-30))
    that should give you next 29 characters starting at Character 30, if there are less than 29 characters left it should put them in instead.
    3: IIf(Len([Comment])>=87,Mid([Comment],59,29),Mid([Comment],59,Len([Comment])-59))
    and so on.
    If you can have less than 29 characters then you will need to use the same type of format as 2 & 3 use in the first column.

    See the attached database for an example.
     

    Attached Files:

  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/940876

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice