Irritating line-break problem after export to txt

[Ivanho]

Regards to all my English is not the best so I apologize in advance to everyone I will try to be as specific as possible.
My problem is the following:
I use the database and need to make a report and export to the TXT UTF-8 on pre-defined format (one-line 21 field separate by the ";")
example:


FNAME;LNAME;DOB;DATE; ...
I made a report on a query that displays the desired result, however, when export the report in txt utf-8 following errors occur:
If for example report is:

John - Senior;Smith; 7/7/55;1/1/11
After exporting I get
John
I noticed that the line-break occurs when using "-" and "space" I am not sure if even more character affects the export.

And another problem is the empty line
example:

FNAME;LNAME;DOB;DATE; ...
FNAME;LNAME;DOB;DATE; ...
FNAME;LNAME;DOB;DATE; ...

FNAME;LNAME;DOB;DATE; ...
FNAME;LNAME;DOB;DATE; ...

Thanks,
Ivan

[rpeare]

Is all of your data stored in Access BEFORE you attempt an export? Or is your data coming from another source, flowing through your Access database, then you're exporting data you got from somewhere else?

In your export function you may want to try using the TRIM function on all your fields Trim(Fieldname). This will strip any extra spaces, carriage returns or line feeds from any of your data and *should* clear up the extra spaces in your text file.

[Ivanho]

Hi,
All data are stored in Access table then I use query to concatenate all 21 field in one field to get the desired result. After that i build the report with just one field and export to txt. In report everything look fine also when i export to ms word everything is fine.
Only when i try to export in txt I receive line-break.(not for all records)

[rpeare]

Did you try using the TRIM function when you were building your string to get rid of the carriage return?

Secondly is there a reason you're building the string before you export it? You should be able to set up an export within access that adds all the ; marks for you with no need to build an export format yourself.

[Ivanho]

Hi,
rpeare Thank you for your reply.
Seems to be a really bad database design!That is the core of all my problem!(This is my first database) Now I'm working on redesigning to avoid rookie mistakes.
I'm not sure that I understand your second question. Is there any other way?
I made a small database with 10 fields as an example I think is the best way to explain the problem.
Avoid a line break but I still have a problem with a blank line.
https://rapidshare.com/files/4287997024/test.accdb

[rpeare]

I don't have access to my 2007 machine right now. If you can upload your test database to this website I can take a look (just zip it first if it's a little large)

[Ivanho]

Ups I did not see the option for attachment.
Thanks

[rpeare]

It's still in access 2007 format (which I can't use at the moment). If you can save the database as an access 2003 format file I can. Just click the windows button in the upper right, hover over save as and a menu should appear just choose access 2003 from that