Results 1 to 4 of 4
  1. #1
    annlcorp
    Guest

    Removing carriage returns?

    Hello!

    I've got an Access 97 file which has been extracted for me from a SQL Server database that holds data that comes from an on-line registration form. The registration form has one large address field where customers just hit Return to move down to enter another line of their address.
    In Access, I want to separate out the different lines of the address into different fields, as per wherever the return is, but I just can't work out how to do it!
    Help please!

    Thanks,

    Ann

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

    Re: Removing carriage returns?

    It would be easy in Access 2000. In 97 it's a lot more work. What you need to do is query out each separate piece of the field into a separate field, right? So you can have a calculated field in your query that reads something like this to return the first part of the address:

    <pre>Address1: Mid([Address],1,instr([Address],chr(13))-1)</pre>


    and this would give you the rest of the string.

    <pre>Remainder: mid([Address],instr(len([Address1]) + 2,[Address], chr(13)) + 1)</pre>


    You can assign the Address1 value to a field in your table using an update query based on the select query and then extract the next part of your address from the remaining string the same way. I would recommend working your way through each target field in a separate expression in a select query so you can see exactly what it will return and modify the expressions as necessary to get the right result.

    It's perfectly possible to do this with code by looping through a recordset as well, and you have more control over the way you extract the values.
    Charlotte

  3. #3
    annlcorp
    Guest

    Re: Removing carriage returns?

    Thanks Charlotte - this is exactly what I needed!
    We may be upgrading fairly soon, so for future reference how would I do it in Access 2000?

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

    Re: Removing carriage returns?

    In Access 2000, you could use the Split() function to break the string into an array using the vbCrLf (Chr(13) + chr(10) as the delimiter. Then you could simply assign each element of the array to a separate field. You probably could do it in a query by repeating the Split over and over, but it would make more sense to do it in code where you could test the Ubound of the array easily and change behavior accordingly.
    Charlotte

Posting Permissions

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