May 14, 2010 at 4:05 am
How can I remove <CR> at end (or beginning) of data entries (in my example in column data_1). I used the following example that is not working). <CR> = carriage return has ascii code 13 -- you cannot type it as " sql = "....." & vbCR & "...." because then RunSQL realy sees a newline and not a character.
sql = "UPDATE table_1 SET data_1 = TRIM(BOTH CHAR(13) FROM data_1)"
DoCmd.RunSQL sql
May 14, 2010 at 6:38 am
I found a solution myself:
sql = "UPDATE table_1 SET data_1 = REPLACE(data_1, CHAR(10), '')"
DoCmd.RunSQL sql
sql = "UPDATE table_1 SET data_1 = REPLACE(data_1, CHAR(13), '')"
DoCmd.RunSQL sql
because you never know if it is a single char(10), a single char(13) or char(13)+char(10) or vice versa .. depending on the client-system who wrote it
but it's strange that you cannot use TRIM with char(10) or char(13) ... normally this is the thing trim should do ? .... or how is it possible to use TRIM here ... any ideas ?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply