Carriage returns appearing in csv export

[bminney]

I use Access 97 to collate visit reports. I then export these as a csv file. Occassionally a rogue carriage return (ascii 13) appears in a text box. This has the effect of knocking the csv out of sync.

I have no idea what is causing this (the carriage return looks like a small box in the csv/excel field).

I'm not an experienced Access user and googling failed to find an answer.

Anyone have any idea what i can do to stop this happening?
Many thanks

 

[gemma-the-husky]

i presume one of the fields you are exporting HAS a carriage return embedded in it.

it might be a different spurious character (eg a tab)

its might be hard to see this, as these are non-printing characters. If you can pinpoint the specific field, edit it directly in the table to strip out the character. See if that helps

 

[bminney]

I can only see the square/oblong character in the spreadsheet after the csv export. I can delete this but by then the damage has been done and following records are out of sequence because the rogue carriage return has created a new record at the wrong place.

I have seen this error before (not by me) in a csv export.

Is there something I can add to the Event Procedure to remove carriage returns?

Cheers
Barrie

 

[boblarson]

You could run an update query on the data using the Replace function to remove any carriage returns.

 

[boblarson]

Actually, I think Access 97 doesn't have the Replace function so you would need to get the code for one. I'll look for it.

 

[boblarson]

Actually, I think Access 97 doesn't have the Replace function so you would need to get the code for one. I'll look for it.

See it here.

 

[bminney]

Wow, quick response!
Now, I looked at the Replace String Function but to be honest I don't know what to do with it.

Where do I place it and how do I get it to find ascii 13 and replace it with nothing?

Sorry if I'm being thick!

Cheers
Barrie

 

[boblarson]

Create an Update Query in the QBE Grid.

In the part that says Update, you would use

FindAndReplace(Chr(13), [YourOriginalFieldName], "")

 

[bminney]

Hello
I tried this but got the error message "Undefined function FindandReplace in expression".
I'm using Access 97.

Thanks for your help so far.
Regards
Barrie

 

[gemma-the-husky]

go back to the original csv file

the cr will be splitting some record into 2

you should know which recordv this is - ie table/field

open the table directly and edit this record to remove any rogue characters

eg just delete the entry and type in the new value

then try the export again

----------
assuming everything is OK , you then have to find out HOW the funny character gets in there in the first place.

 

[boblarson]

Hello
I tried this but got the error message "Undefined function FindandReplace in expression".
I'm using Access 97.

Thanks for your help so far.
Regards
Barrie

That's because you have to copy that function (from the link I posted where I said "See it here") into a standard module (not form or report module). Name the module something different from the function that you copied in.

 

[boblarson]

assuming everything is OK , you then have to find out HOW the funny character gets in there in the first place.

A good chance of that is someone holding their control key down and hitting their Enter/Return key.

 

[gemma-the-husky]

yep - that is how you can enter a CR into a textbox. (ctrl+enter)

 

[bminney]

Well now I know what's causing it!! Useful advice, thanks.
Barrie