[] Character stoping query

[scottemotive]

Hi

I have a text box bound to a field in a table, now ive noticed boxes appearing at the end of some lines (where i think users are pressing enter or shift+enter) in the text box
its kinda like [] now when i run a query on this table it does not output the full number of records i think it does not show anything after a record that contains one of these characters
does anyone know anything about this and suggest a method to fix it, ive been through my whole table looking for these characters but cant seem to output the full table still

thanks

 

[Uncle Gizmo]

I am sure there is a better way than this, but it's the only thing that springs to mind at the Moment.

Write a function that looks at each character in your string in turn, converts each character into its ASCII value, compare that asc value to a list of ASCII values that are acceptable characters. Where the there is no matching ASCII value in your legal characters list, then replace the character, either with a zero length character "" alternatively you could replace the character with a text character you know that you don't use the tide Character (~) is often used in this situation.

I would say before you start, that the function itself may not recognize your Special characters and cause problems. So it may be sensible to try a little test before you spend too much time on it.

Finally, I have had some experience with extracting particular characters from strings, there is some code which you might find helpful on my website, have a look at item five on this web page. Also the post code checking routine has some character detection routines in it.

 

[Uncle Gizmo]

The only thing that springs to mind, (and there must be a better way than this) is to loop through your text with a character checking routine. Basically what you do is you convert each character into its ASCII code, compare that ASCII code against a list of legal characters. Where there is no match, replace the character either with an empty string "" or a character you do not use like the tide {~} this allows you to see what you've done.

First of all there is no guarantee that this will work, the strange characters may interfere with how the function works. So it would be a good idea to do a small test before you spend too much time on it.

I have done some character checking routines in the past, and there is an example available on my website See item number five. also the post code checking routine has some other character checking code in it.

 

[Jon K]

If the user pressed Ctrl+Enter in the text box, in Access 2000 or higher you can run an Update Query to replace the Carriage Return and Line Feed characters with a Space:-

UPDATE [TableName] SET [FieldName] = Replace([FieldName],Chr(13) & Chr(10)," ")
WHERE InStr([FieldName],Chr(13) & Chr(10));


In some versions of Access 2000, you can't use the Replace() function directly in a query. You need to create a wrapper function in a module:

Public Function MyReplace(sText)
    MyReplace = Replace(sText, Chr(13) & Chr(10), " ")
End Function

and use the wrapper function in the query:

UPDATE [TableName] SET [FieldName] = MyReplace([FieldName])
WHERE InStr([FieldName],Chr(13) & Chr(10));
.