removing trailing returns

[Southern at Heart]

I have the below routine to remove any trailing returns from the specified
field. The Table name and the Field name in that table are passed to this
code. Would it be easy to modify this code so that if I passed the * sign or
the word "ALL" or something like that, as the strField, that the code would
remove the trailing returns from all the fields in the specified table? This
would save me LOTS of extra lines.
thanks,
SouthernAtHeart

Sub Remove_Trailing_Returns(strTable As String, strField As String)
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
strSQL = "UPDATE " & strTable & " SET " & strTable & "." & strField & " =
Left(" & _
strField & ",Len(" & strField & ")-2) WHERE (((" & strTable & "." & _
strField & ") Like ""*"" & Chr(13) & Chr(10)));"
Set qd = db.CreateQueryDef("", strSQL) ' create an unnamed, unsaved query
StartOver:
qd.Execute
Debug.Print qd.RecordsAffected
If qd.RecordsAffected <> 0 Then GoTo StartOver
End Sub

 

[Dirk Goldgar]

I have the below routine to remove any trailing returns from the specified
field. The Table name and the Field name in that table are passed to this
code. Would it be easy to modify this code so that if I passed the * sign
or
the word "ALL" or something like that, as the strField, that the code
would
remove the trailing returns from all the fields in the specified table?
This
would save me LOTS of extra lines.
thanks,
SouthernAtHeart

Sub Remove_Trailing_Returns(strTable As String, strField As String)
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
strSQL = "UPDATE " & strTable & " SET " & strTable & "." & strField & " =
Left(" & _
strField & ",Len(" & strField & ")-2) WHERE (((" & strTable & "." & _
strField & ") Like ""*"" & Chr(13) & Chr(10)));"
Set qd = db.CreateQueryDef("", strSQL) ' create an unnamed, unsaved query
StartOver:
qd.Execute
Debug.Print qd.RecordsAffected
If qd.RecordsAffected <> 0 Then GoTo StartOver
End Sub

Well, you can't automatically do it for all fields; you'd want to restrict
it to text and memo fields. Here's a completely untested, "air code"
version of your routine:

'----- start of air code -----
Sub Remove_Trailing_Returns(strTable As String, strField As String)

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strFieldList As String
Dim strFieldName As String
Dim I As Long

Set db = CurrentDb

If strField = "*" Then
' Make a list of the table's text and memo fields.
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
With fld
If .Type = dbText Or .Type = dbMemo Then
strFieldList = strFieldList & ";" & .Name
End If
End With
Next fld
If Len(strFieldList) > 0 Then
strFieldList = Mid(strFieldList, 2)
End If
Set tdf = Nothing
Else
' Our list will consist of only the field that was passed.
strFieldList = strField
End If

' Now process each field in the list.
Do Until Len(strFieldList) = 0

' Grab the next field name off the list.
I = InStr(strFieldList, ";")
If I = 0 Then
strFieldName = strFieldList
strFieldList = vbNullString
Else
strFieldName = Left(strFieldList, I - 1)
strFieldList = Mid$(strFieldList, I + 1)
End If

' Update the table to remove trailing returns from
' the current field.
Do
strSQL = _
"UPDATE [" & strTable & "] " & _
"SET [" & strFieldName & "] = Left([" & _
strFieldName & "], Len([" & strFieldName & "])-2) " & _
"WHERE [" & strFieldName & "] Like '*' & Chr(13) & Chr(10)"

db.Execute strSQL, dbFailOnError
'Debug.Print db.RecordsAffected

Loop Until db.RecordsAffected = 0

Loop

Set db = Nothing

End Sub
'----- start of air code -----

 

[Southern at Heart]

That was great! I tweaked it with a couple (( and a ;
and it works perfect! That'll save a lot time

Another similar question:

Could you alter this a little so that it works the same way only instead of
removing the trailing returns from the end of the field/fields, it removes
the two characters
^b (or ^B) from any part of the field. Baxsically, replace ^b or ^B with
nothing.
A hundred thanks,
SouthernAtHeart

ps. Here's the working code in case someone else finds it helpful

Function Trailing_Returns(strTable As String, strField As String)
'REMOVE TRAILING RETURNS FROM A TABLE FIELD
'The Table Name and Field Name in that table are passed to this function
'If * is passed as the Field, then all text and memo fields will be cleared
Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strFieldList As String
Dim strFieldName As String
Dim I As Long

Set db = CurrentDb

If strField = "*" Then
' Make a list of the table's text and memo fields.
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
With fld
If .Type = dbText Or .Type = dbMemo Then
strFieldList = strFieldList & ";" & .Name
End If
End With
Next fld
Debug.Print strFieldList
If Len(strFieldList) > 0 Then
strFieldList = Mid(strFieldList, 2)
End If
Set tdf = Nothing
Else
' Our list will consist of only the field that was passed.
strFieldList = strField
End If

' Now process each field in the list.
Do Until Len(strFieldList) = 0

' Grab the next field name off the list.
I = InStr(strFieldList, ";")
If I = 0 Then
strFieldName = strFieldList
strFieldList = vbNullString
Else
strFieldName = Left(strFieldList, I - 1)
strFieldList = Mid$(strFieldList, I + 1)
End If

' Update the table to remove trailing returns from
' the current field.
Do
strSQL = _
"UPDATE [" & strTable & "] " & _
"SET [" & strFieldName & "] = Left([" & _
strFieldName & "], Len([" & strFieldName & "])-2) " & _
"WHERE ((([" & strFieldName & "]) Like '*' & Chr(13) &
Chr(10)));"

db.Execute strSQL, dbFailOnError
'Debug.Print db.RecordsAffected




Loop Until db.RecordsAffected = 0

Loop

Set db = Nothing

End Function

 

[Southern at Heart]

Something would be settle many issues I come up against:
If I had a function that I could pass 4 arguments to:
strTable
strField
strFind
strReplace

I could use it to 'delete' a strFind by passing "" in strReplace
This would be so handy, especially if it incorporated Goldgar's code, where
I could use * for all the fields
I often need to find/replace chrs like ^, <, >, \
If seems like this is over my head. If someone can do this for me I'd sure
appriciate it. I understand if this is asking too much. This may be more
complicated than I realize.

 

[Dirk Goldgar]

That was great! I tweaked it with a couple (( and a ;
and it works perfect!

Did you do that because you found it didn't work as originally posted, or
just because you thought you should? because I think it ought to work the
way I posted it. Note that you don't need a trailing semicolon in a SQL
statement, and Access likes to add lots of unnecessary parentheses when you
build queries in the design grid.

Another similar question:

Could you alter this a little so that it works the same way only instead
of
removing the trailing returns from the end of the field/fields, it removes
the two characters
^b (or ^B) from any part of the field. Baxsically, replace ^b or ^B with
nothing.

Literally "^b" and "^B"?

Removing strings from any part of a field is simpler than removing them from
just the end of the field, as you can just use the Replace function, and
don't need to loop until they're all gone.

 

[Southern at Heart]

Did you do that because you found it didn't work as originally posted, or

just because you thought you should?

....to make it work...so I thought. It wasn't working, but now that you
mention it, I think it was an error in my table that made me think it wasn't
working. I'll keep that in mind about not needing those extra characters.

Literally "^b" and "^B"?

Yes.
I thought it'd be easy, too, but I couldn't get a generic replace function
to work with ^b or other special characters, and also work with replacing
simple letter strings.
I'm really a newbie at this all...

when you say the replace function, do you mean something different than
using an update SQL command that has the replace command in it?

Thanks,
SouthernAtHeart

 

[Southern at Heart]

I learn how to build sql from making a query, than using the form SQLtoVBA to
get something like this:

strSQL = "UPDATE tblName SET tblName.Name = Replace([Name],""^b"","" "") "
& _
"WHERE (((tblName.Name) Like ""*[^b]*""));"

....but here, with this special char ^, if I pass strFind as "^b", it fits
fine in:
Replace([Name],""^b"","" "")...

....but it doesn't fit in:
Like ""*[^b]*""));"
....here, because of needing those bracket signs. But if I were to pass a
normal text string, I don't need those [] signs.

I'm about to give up on it and just keep coding it out each time.

 

[John W. Vinson]

Something would be settle many issues I come up against:
If I had a function that I could pass 4 arguments to:
strTable
strField
strFind
strReplace

I could use it to 'delete' a strFind by passing "" in strReplace
This would be so handy, especially if it incorporated Goldgar's code, where
I could use * for all the fields
I often need to find/replace chrs like ^, <, >, \
If seems like this is over my head. If someone can do this for me I'd sure
appriciate it. I understand if this is asking too much. This may be more
complicated than I realize.

It's actually EASIER than you think.

There is a builtin Access function named Replace:

Replace(Oldstring, StringToReplace, StringToReplaceItWith)

For example, Replace("Some^bstring", "^b", " ")

will replace all occurances of the string ^b with a single blank, yielding
"Some string".

You can use this function in an Update query.

So your function could be

Public Function GenericReplace(strTable, strField, strFind, strReplace) _
As Boolean
Dim strSQL As String
On Error GoTo Proc_Err
strSQL = "UPDATE [" & strTable & "]" _
& " SET [" & strField & "]" _
& " = Replace([" & strField & "], """ & strFind & """, """ & strReplace &
""")" _
& " WHERE [" & strField & "] LIKE ""*" & strFind & "*"";"
Currentdb.Execute strSQL, dbFailOnError
GenericReplace = True
Proc_Exit:
Exit Function
Proc_Err:
MsgBox "Error " & Err.Num & " in GenericReplace:" & vbCrLf _
& Err.Description
GenericReplace = False
Resume Proc_Exit
End Sub

If you pass the function arguments like

Success = GenericReplace("MyTable", "FieldA", "^b", " ")

the SQL string will evaluate to

UPDATE [MyTable] SET [FieldA] = Replace([FieldA], "^b", " ") WHERE [FieldA]
LIKE "*^b*";

This will NOT work with * as a fieldname without more code, and that code
would need to be a bit sophisticated; you'll need to loop through the table's
fields, skipping all but the Text or Memo fields.

 

[John W. Vinson]

...but here, with this special char ^, if I pass strFind as "^b", it fits
fine in:
Replace([Name],""^b"","" "")...

...but it doesn't fit in:
Like ""*[^b]*""));"
...here, because of needing those bracket signs.

WHY do you feel that you need the brackets? ^ is not an Access SQL wildcard;

LIKE "*^b*"

finds records containing the text string ^b with no complaints and no errors;
the brackets are not required.

 

[John Spencer]

All you need to change is the SQL string ( and if you are using a version of
Access later than Access 2000, you can use the Replace function.

strSQL = _
"UPDATE [" & strTable & "] " & _
" SET [" & strFieldName & "] = " & _
" REPLACE([" strFieldName & "], '^B','') " & _
" WHERE [" & strFieldName & "] Like '*^B*' "

That may give you an error if all the field contains is ^B or ^b and spaces.
If the field accepts zero-length strings then no error, but if the field does
not allow zero-length strings then you will get an error. In latter case, you
have some choices

ONE: run a separate query first that sets any field that contains ^B and zero
to many spaces and nothing else to null.

strSQL = _
" UPDATE [" & strTable & "] " & _
" SET [" & strFieldName & "] = NULL " & _
" WHERE Trim([" & strFieldName & "]) = '^B' "

TWO: handle the problem with an IIF statement in the query.
strSQL = _
"UPDATE [" & strTable & "] " & _
" SET [" & strFieldName & "] = " & _
" IIF(Trim([" & strFieldName & "])='^b'," & _
"Null,REPLACE([" strFieldName & "], '^B','')) " & _
" WHERE [" & strFieldName & "] Like '*^B*' "


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

That was great! I tweaked it with a couple (( and a ;
and it works perfect! That'll save a lot time

Another similar question:

Could you alter this a little so that it works the same way only instead of
removing the trailing returns from the end of the field/fields, it removes
the two characters
^b (or ^B) from any part of the field. Baxsically, replace ^b or ^B with
nothing.
A hundred thanks,
SouthernAtHeart

ps. Here's the working code in case someone else finds it helpful

Function Trailing_Returns(strTable As String, strField As String)
'REMOVE TRAILING RETURNS FROM A TABLE FIELD
'The Table Name and Field Name in that table are passed to this function
'If * is passed as the Field, then all text and memo fields will be cleared
Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strFieldList As String
Dim strFieldName As String
Dim I As Long

Set db = CurrentDb

If strField = "*" Then
' Make a list of the table's text and memo fields.
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
With fld
If .Type = dbText Or .Type = dbMemo Then
strFieldList = strFieldList & ";" & .Name
End If
End With
Next fld
Debug.Print strFieldList
If Len(strFieldList) > 0 Then
strFieldList = Mid(strFieldList, 2)
End If
Set tdf = Nothing
Else
' Our list will consist of only the field that was passed.
strFieldList = strField
End If

' Now process each field in the list.
Do Until Len(strFieldList) = 0

' Grab the next field name off the list.
I = InStr(strFieldList, ";")
If I = 0 Then
strFieldName = strFieldList
strFieldList = vbNullString
Else
strFieldName = Left(strFieldList, I - 1)
strFieldList = Mid$(strFieldList, I + 1)
End If

' Update the table to remove trailing returns from
' the current field.
Do
strSQL = _
"UPDATE [" & strTable & "] " & _
"SET [" & strFieldName & "] = Left([" & _
strFieldName & "], Len([" & strFieldName & "])-2) " & _
"WHERE ((([" & strFieldName & "]) Like '*' & Chr(13) &
Chr(10)));"

db.Execute strSQL, dbFailOnError
'Debug.Print db.RecordsAffected




Loop Until db.RecordsAffected = 0

Loop

Set db = Nothing

End Function

 

[Dirk Goldgar]

I learn how to build sql from making a query, than using the form SQLtoVBA
to
get something like this:

strSQL = "UPDATE tblName SET tblName.Name = Replace([Name],""^b"","" "")
"
& _
"WHERE (((tblName.Name) Like ""*[^b]*""));"

...but here, with this special char ^, if I pass strFind as "^b", it fits
fine in:
Replace([Name],""^b"","" "")...

...but it doesn't fit in:
Like ""*[^b]*""));"
...here, because of needing those bracket signs. But if I were to pass a
normal text string, I don't need those [] signs.

I'm about to give up on it and just keep coding it out each time.

I don't know what SQLtoVBA is, but I think it must be a utility to convert a
SQL query to VBA code that builds a SQL string. I think that utility must
be adding the brackets, presumably as some sort of protection against
special characters. However, in Jet SQL, "^" is not a special character.
You should either adjust the operation of this SQLtoVBA utility, or look at
the statements it returns and take out what is unnecessary or incorrect.

 

[Dirk Goldgar]

when you say the replace function, do you mean something different than
using an update SQL command that has the replace command in it?

I was referring to the Replace() function, a VBA function that can be called
from an Access query. I believe John Vinson and John Spencer have posted
showing how that would work.