Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Kansas
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split Field, link to table (2000)

    I have a table (created by others) that contains a 10 character field with up to 5 codes (2 character) stuffed into that field. I need to print on separate lines each code and an associated meaning. Some fields have only one code, others have all 5. Is there a way to do this without having a programmer on hand? I can make the link to the associated table, but am unsure (flat don't know) how to split the individual 2 char codes from the field.
    Suggestions??

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

    Re: Split Field, link to table (2000)

    You can create a query that splits the field into 5 parts.

    If the field is named MyField, the first part would be Left(MyField,2), the second part Mid(MyField, 3, 2), and so on.

    Here is the SQL statement of such a query:

    SELECT Left([MyField],2) AS P1, Mid([MyField],3,2) AS P2, Mid([MyField],5,2) AS P3, Mid([MyField],7,2) AS P4, Mid([MyField],9,2) AS P5
    FROM MyTable

    Of course, you can add other fields from the table to the query.

    You can create a separate table containing the 2 character codes and their descriptions.

    The parts are named P1 to P5. On a form or report based on this query, you can put text boxes bound to P1 to P5 below each other. Besides these put combo boxes bound to P1 to P5, with a lookup to the separate table, so that the combo boxes will display the descriptions.

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Kansas
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Field, link to table (2000)

    Thank you. It had not occured to me that I could use SQL (not that I am fluent in SQL).

    Another question on the output. My final report will be sent to a text file. Why does the access report show the field name on a line, then the value on a new line below that one? Is there a setting for text that I have missed? I just want to create a flat file like this:

    fieldName fieldvalue field name fieldvalue
    fieldName fieldValue
    field...

    Why, you ask? The text will be imported into a text only database for data mining - it only reads text.

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

    Re: Split Field, link to table (2000)

    There is no need to use SQL. People on this forum often give the SQL of a query because it's text and can be copied and pasted easily. You can create the query in the query design grid too.

    Perhaps others will correct me, but I think that exporting a report isn't very dependable. If possible, try to export a query.
    Attached Images Attached Images

Posting Permissions

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