Update Query - Remove carrier returns

[zashaikh]

I am trying to write an update query to:


1. Remove all carrier returns my field
and
2. Remove all spaces that occur before "20" in my field. In otherwords, I want " 20" to become "20"

I tried this: UPDATE Table1 SET Table1.txtDetails = REPLACE([txtDetails], Chr(13) & Chr(10),"");

I know the above query doesnt account for "removing spaces in front of '20'

But MS access says it didnt update one field due to a conversion failure.

[orange]

The space in " 20" is not caused by a Carriage return line feed combination.

Not exactly sure what text you are dealing with, but to remove that space you could try

UPDATE Table1 SET Table1.txtDetails = REPLACE([txtDetails], " 20","20");

I'd use a SELECT query to ensure there were not "unwanted" changes.

see https://www.techonthenet.com/access/...ng/replace.php for details on Replace.

[zashaikh]

Orange,

1. The data in txtDetails always starts with a space and/or several spaces and/or a carrier return, followed by a 25 digit key. The 25 digit key ALWAYS starts with "20".

I am aware the <space> next to twenty is not a Carriage Return line. However, I do have carriage returns along with the space."

For example, I have:

"


2017-45-46353y7-246523
"

In addition, I may have more than one space. So I may have " 20". Thus, the SQL you posted would not account for multiple spaces.

[davegri]

How about the TRIM function instead of REPLACE to remove leading and trailing spaces.

[orange]

Replace will remove all identified characters.

Try this

Sub xspace()
'chr(10) new line LF
'chr(13) carriage return
    Dim x As String: x = "a" & "a" & Chr(13) & Chr(10) & "     2017ghjkl   plkjhgfd" & Chr(13) & Chr(10) & "axxx"
   
    x = Replace(x, "a", "")
    Debug.Print "1 " & x

    x = Replace(x, Chr(13) & Chr(10), "")
    Debug.Print "2 " & x

    x = Replace(x, " ", "")
    Debug.Print "3 " & x
End Sub

Results:

1 
     2017ghjkl   plkjhgfd
xxx
2      2017ghjkl   plkjhgfdxxx
3 2017ghjklplkjhgfdxxx

To do multiple replaces (different criteria)

Sub yspace()
'chr(10) new line LF
'chr(13) carriage return
    Dim x As String: x = "a" & "a" & Chr(13) & Chr(10) & "     2017ghjkl   plkjhgfd" & Chr(13) & Chr(10) & "axxx"
    x = Replace(x, "a", "")
    Debug.Print "1 " & x
    x = Replace(Replace(x, "a", ""), Chr(13) & Chr(10), "")
    Debug.Print "2 " & x
    x = Replace(Replace(Replace(x, "a", ""), Chr(13) & Chr(10), ""), " ", "") '<<++++++
    Debug.Print "3 " & x
End Sub

Result:

1 
     2017ghjkl   plkjhgfd
xxx
2      2017ghjkl   plkjhgfdxxx
3 2017ghjklplkjhgfdxxx  <=====All criteria used in #3 here

[zashaikh]

Davegri, can I use the trim function to delete everything that comes before the first "20" identified in a string of text?

[zashaikh]

I am a little lost with this Orange. This code would only work if the "20" number was the same. The "20" is the only thing that remains stagnant. It can be 203452348 one day and 203185748 the next.

[orange]

see my updated post #5 to remove the unwanted characters (incrementally, or multicriteria on one line)

also, is it ????
chr(10) or
chr(13) or
chr(13) & chr(10)

[davegri]

Davegri, can I use the trim function to delete everything that comes before the first "20" identified in a string of text?

As I mentioned, the trim function will remove leading and trailing (only) spaces from a field.
If you have used REPLACE to replace the CR and LF with spaces, a later trim will remove them.

[zashaikh]

I see. Thank you for the tip Davegri.

And Orange, the problem is that I need to use a query. Not VBA (at least, I dont know how to use VBA in a query. I only use it in forms to make my life easier)

My query needs to be an update query that will remove all CR & LF in a specified field across my entire table.

[orange]

Is it only the spaces before the 20? Or all spaces and cr Lf?
I don't see other spaces in your sample so

UPDATE Table1 SET Table1.txtDetails = REPLACE(REPLACE([txtDetails], Chr(13) & Chr(10),"")," ","");

REPLACE([txtDetails], Chr(13) & Chr(10),"") is executed first and removes All Chr(13) & chr(10)

REPLACE( XXX,," ","") acts on the string after the first replace has been done.

Test on a sample before modifying important data.

[zashaikh]

I only care about the spaces before "20". I will give this a try tomorrow. Thanks

[zashaikh]

Orange, I tried the code you posted in #11. But I got the following error.



After I pressed 'yes', no changes were made

[zashaikh]

Is there not a way to delete every character that comes before the first "20" in a field?

[orange]

It seems there may be some issues with your Table1.
Please show us the definition of the table including the design (data types) and some sample records.