I've imported data from a database - containing separate fields for
name, address and postcode, into one single text field in another
database (I know that's rotten database practice, but I was asked to
do this at work).

I concatenated the fields in the "donor database then mapped the
single concatenated field to the recipient single text field on
import. I now have a field containing name, address and postcode, with
spaces between each element(I inserted them), but of course there is
no wrapping. I now have (example)

MrTest Name Test Address TestPostcode

in a single text field.

I'd like to bring this dbase file into Access and use a function/vb to
insert the carriage return at the end of each element so text is
wrapped correctly. Does anyone know how to do this please?

I could envisage writing a character (eg a comma or asterisk) at the
end of each piece of data, prior to performing the concatenation so
that this character could be used as a criterion for a function
inserting a break, but I'm not very experienced in parsing strings
etc, so the idea hardly solves my problem.

Any help would be appreciated.

Thanks
Greg

gregory.oxnard@ntlworld.com (Greg Oxnard) wrote in 
news:7dee7a8c.0308161004.443136a4@posting.google.com:

> I've imported data from a database - containing separate fields for
> name, address and postcode, into one single text field in another
> database (I know that's rotten database practice, but I was asked to
> do this at work).
> 
> I concatenated the fields in the "donor database then mapped the
> single concatenated field to the recipient single text field on
> import. I now have a field containing name, address and postcode, with
> spaces between each element(I inserted them), but of course there is
> no wrapping. I now have (example)
> 
> MrTest Name Test Address TestPostcode
> 
> in a single text field.
> 
> I'd like to bring this dbase file into Access and use a function/vb to
> insert the carriage return at the end of each element so text is
> wrapped correctly. Does anyone know how to do this please?
> 
> I could envisage writing a character (eg a comma or asterisk) at the
> end of each piece of data, prior to performing the concatenation so
> that this character could be used as a criterion for a function
> inserting a break, but I'm not very experienced in parsing strings
> etc, so the idea hardly solves my problem.
> 
> Any help would be appreciated.
> 
> Thanks
> Greg

I expect there will be some suggestions made about parsing functions.

I would point out that if you can connect to your dBase source using ADO 
and create an ADODB recordset, you can use
GetString(adClipString, 1, vbNewLine)
to return each address already formatted as you describe, and can then use 
some query string to append that to your recipient? table.

-- 
Lyle

Another way would be to use a fixed length string.  Say the name portion is
always 30 characters, the address starts at position 31 and is always the
next 50 characters and so on.  In the importing database you can do what
ever you want because you'll know the start and stop position of each
element of the string.

Example Export String:
"FirstName M LastName                            Address Starts at position
31                             Postcode starts at position 81
"

In the receiving database you could then use the portion of the string you
want such as the address: strAddress = MID(ImportString,31,50)
Mid(string, start[, length])



"Bob Quintal" <bquintal@generation.net> wrote in message
news:Xns93D99DF604FD5Bquintal@216.168.3.44...
> gregory.oxnard@ntlworld.com (Greg Oxnard) wrote in
> news:7dee7a8c.0308161004.443136a4@posting.google.com:
>
> > I've imported data from a database - containing separate fields for
> > name, address and postcode, into one single text field in another
> > database (I know that's rotten database practice, but I was asked to
> > do this at work).
> >
> > I concatenated the fields in the "donor database then mapped the
> > single concatenated field to the recipient single text field on
> > import. I now have a field containing name, address and postcode, with
> > spaces between each element(I inserted them), but of course there is
> > no wrapping. I now have (example)
> >
> > MrTest Name Test Address TestPostcode
> >
> > in a single text field.
> >
> > I'd like to bring this dbase file into Access and use a function/vb to
> > insert the carriage return at the end of each element so text is
> > wrapped correctly. Does anyone know how to do this please?
>
> Go back to the idi\\\person who told you to import the data as a single
> field, then tell them to fix it you need to re-import as separate
> fields. Then you can generate reports and mailing lists properly in the
> future, using concatenation to put the data on one line when
> appropriate. :)
>
> Otherwise, you'll need to write code to break the data at the
> appropriate points, concatenate Chr(13) & chr(10) & the next part of
> your string, another Chr(13) & chr(10) & the next part of your string
> and so on until you've completed the line.
>
> A lot of useless work, IMO.
>
>
>
> >
> > I could envisage writing a character (eg a comma or asterisk) at the
> > end of each piece of data, prior to performing the concatenation so
> > that this character could be used as a criterion for a function
> > inserting a break, but I'm not very experienced in parsing strings
> > etc, so the idea hardly solves my problem.
> >
>
> Given that the end of each piece of data may be different in length, may
> contain embedded spaces and possibly other punctuation, it's not easy
> for even an experienced programmer.
>
> > Any help would be appreciated.
> >
> > Thanks
> > Greg
>
> Bob