Results 1 to 8 of 8
  1. #1
    itchyray
    Guest

    Summation to Access2000

    Exported data from Summation as a text file. Opened in WORD and replaced multi-entry separators w/unique text identifier in order to keep data w/in same field w/in ACCESS. Imported file to ACCESS. How do we replace unique text identifier in ACCESS w/ hard return.

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

    Re: Summation to Access2000

    Don't. That is a totally non-relational and non-normalized approach to data. If you keep multiple entries in a single field, there is no point in using Access, and you might as well use Excel instead.

    If you want to normalize the data, you'll need to break those individual values out into separate records in another table and and link the two tables on the unique key in the first table, which would be a foreign key in the second.
    Charlotte

  3. #3
    dlitt
    Guest

    Re: Summation to Access2000

    Summation is a non-relational case management tool. There are instances where we want to export the data from Summation to an Access database. The field in Summation may contain a list of information (such as recipients to a particular document) separated by hard returns which, when exported, are separated by a semi-colin. We search and replace the semi-colin with a unique identifier so that the data is in the appropriate field when imported to Access. We want to be able to replace the unique identifier with a hard return once it is in Access so that it doesn't wrap. Basically we're asking a text formatting question.

    We know it's not exactly the appropriate way to use Access, but it gets us where we want to go when we're using Summation. We do use Access appropriately most of the time.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Summation to Access2000

    I think what we are talking about is a multiline field rather than a multifield entry.

    You could try replacing the unique identifier with Chr(10) which is a linefeed or Chr(13) which is a return.
    David Grugeon
    Brisbane Australia

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

    Re: Summation to Access2000

    Well, sometimes you do have to use a chisel to drive nails if that's all you have handy for the job.[img]/w3timages/icons/nuts.gif[/img]

    In this case, if you're using Access 2000, it's easy. If you're using Access 97, it's work.

    With Access 2000, you can use the built-in Replace() function to find every instance of the placeholder you inserted in that field and replace it with the vbNewLine or vbCrLf or chr(10) & chr(13) or just the chr(10) or chr(13).

    If you're using Access 97, you have to use a user-defined function that uses the InStr() function to find every instance of the placeholder and replace it with the appropriate carriage return as above. Once InStr() returns a zero, meaning that it can't find any more placeholders, your custom function would return the modified string for that field.
    Charlotte

  6. #6
    dlitt
    Guest

    Re: Summation to Access2000

    Thank you so much for helping us out!

  7. #7
    dlitt
    Guest

    Re: Summation to Access2000

    I got so excited about your response that I just had to try it right then. Well, I guess you're going to have to explain it like I'm a two-year old because I can't find that built-in Replace() function. Give me the step-by-step version and I'll see if I can follow it.

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

    Re: Summation to Access2000

    Replace is a built-in VBA string function in Access 2000. You can find it in help if you use the Answer wizard and type in Replace, then hit Search.

    Replace is used like this:

    strOutput = Replace("abc^^def^^ghi","^^",vbNewLine,1)

    Which will return this:

    abc
    def
    ghi

    Does that help?
    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
  •