Removing Record that contains a strange character

[chris9]

I'm getting a unique character when I import my text file. Here it is: 
If I want to create a SQL statement to automatically remove these records that contain  in one of the fields, how would I do that? I would be putting the SQL statement in a form that gets executed when the button is clicked.
Thanks in Advanced.

 

[nealvenables]

The chances are that the box is a linefeed and/or carriage return so it may be more appropriate to change the "Enter Key Behaviour" property of your text-box so that the data is displayed properly.

However, if you actually want to delete any row with this character in it, the SQL statment would be something like:

DELETE Table1.*
FROM Table1
WHERE (((InStr(1,[TextField],Chr$(13)))>0));

Alternatively, if you just want to remove these boxes from your data, you will need to use the REPLACE function in a VBA module. You could get your form to clean this data every time it is displayed with something along these lines (posted on the fly, so you'll need to tweak and debug).


private sub form_current()

me.txtBox = replace(me.txtBox, chr$(13), "&quot
me.txtBox = replace(me.txtBox, chr$(10), "&quot

end sub