Blank row below data in singular data field (1 Viewer)

[tinyevil777]

Afternoon all!

I hold cross reference data in a table, in a singular 'Text' field. An issue has arisen when performing data exports, it seems that some data has a 'blank line' underneath the actual data. Apologies for the vagueness in my explanation, i'm not quite sure how to put it across. See the screenshot attached.

If i remove the line from the data, then all issues are gone. However, there are around 700,000 cross references, therefore it's not really an option to do it manually. Also, when you view the table in datasheet view, you're unable to even see the extra row in the singular field. Very confusing.

I've tried removing it by SQL, but can't seem to get it right. I tried selecting where RIGHT(MyField,1) = '' however it returned nothing. Not sure what the syntax would be.

Has anyone ever come across this before?

Thank you all in advance!

 

[tinyevil777]

Apologies... here's the screenshot.

 

[spikepl]

You probably have a vbcrlf in the line= Chr(13) & chr(10).

 

[tinyevil777]

You probably have a vbcrlf in the line= Chr(13) & chr(10).

I see, is there a way of removing these lines by SQL? Apologies for my ignorance, this is a totally new issue to me.

 

[spikepl]

With an update query, and using the Replace function, where you replace the offending character or characters by "". But before that, check what is at the end by a select query first: Asc(Right(MyField,1)) and also check the content of the second last character in the same way.

 

[tinyevil777]

So... The Update query would be something along the lines of...

Update MyTbl Set MyField = TRIM(RIGHT(MyField,1))

Or am i way off? :/

 

[spikepl]

LOL - you will wipe out your data like that.

Left(MyField,Len(MyField)-1)

YEah, maybe that is simpler. Run that on a COPY of your data. And if not enough, then run it again (if there are two characters at the end).