Access 2007 Export Problem - paragraph mark

[thread]

I am trying to export information from an Access query to a tab delimited text file so that I can import the info into another database. Here is the problem - I need to import multiple address lines into one field. The database accepts "/n" to determine where to split the line. So - I run my query - change my char(10) and char(13) to "/n" and everything looks lovely. I export it to a .txt file and an invisible paragraph mark is added so that the record with the "/n" is pushed into 2 records with the second being in the completely wrong columns. If I copy the lines directly from Access into Word to see the marks - my address lines look great. When I copy the exported lines into Word - voila - a paragraph mark shows up - it appears that Access is creating the paragraph mark as it exports - am I insane or has anyone else experienced this?

I've also tried exporting into Excel with the same results. Not only does the darn thing keep showing up where I don't want it. I can't seem to get rid of it after with any kind of find and replace.

Thanks for listening to my rant. - Gail

 

[Remou]

When you say you change 13 and 10 to /n, how are you doing this? Anything like this:

Replace(Field,(Chr(13)+Chr(10)),"/n")

 

[thread]

Looks like this:

Replace(Trim([ADDRESSBLOCK]),Chr(13) & Chr(10),"/n")

 

[Remou]

I wonder if Chr(10) or Chr(13) is occuring by itself?

It may be worth trying:


Replace(Replace(Trim([ADDRESSBLOCK]),Chr(13) & Chr(10),"/n"),Chr(13),"")

Or

Replace(Replace(Trim([ADDRESSBLOCK]),Chr(13) & Chr(10),"/n"),Chr(10),"")

 

[thread]

I'll try it - however, I'm suspicious because of the difference in the results I get when cutting and pasting the text into Word pre and post export - they look very different. Worth a try though - I'll let you know - Thanks - Gail

 

[PHV]

Why using word for a tab delimited text file ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thread]

I'm not using Word - just pasting the offensive text into word because its the only place I can see what I'm dealing with.

Remou - I tried your fix to no avail - here is the text from the same address:

Direct from Access after the Replace:

17A, Blk C, The Crescent/n11 Ho Man Tin Hill Road/nKowloon

And in my Excel file after export:

17A, Blk C, The Crescent
/n11 Ho Man Tin Hill Road
/nKowloo

The same thing happens if I export directly to a tab delimited file.

 

[PHV]

So, what is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thread]

I tried:

Replace(Trim([ADDRESSBLOCK]),Chr(13) & Chr(10),"/n")

and

Replace(Replace(Trim([ADDRESSBLOCK]),Chr(13) & Chr(10),"/n"),Chr(13),"")

and

Replace(Replace(Trim([ADDRESSBLOCK]),Chr(13) & Chr(10),"/n"),Chr(10),"")

all three give me the clean address in Access - no nasty character before the /n - when I export them, I get the extra character.

 

[PHV]

I meant, your actual code doing the export.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thread]

I'm just using the export function in Access 2007 - export to text file and export to Excel.

 

[traingamer]

Here is the problem - I need to import multiple address lines into one field.

Why not separate the fields with another character like '~' or '^' and then split the addresses in the import into the other database?


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[thread]

Yep - thanks Greg - I could do that. I'm being extremely obstinate here. It just seems like it should work - and I can't figure out what's happening....

 

[traingamer]

Are the <cr><lf> in your data or are they added via the query itself?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[thread]

in the data.