Replace Function for all fields in Table 1

[spider8]

HI,
I am trying to find the easiest way to clean out all chr(10) and chr(13) from a table with alot of fields. The replace function works, but I can only update one field at a time with that (at least the way I'm doing it). I also tried the switch function, but something must be wrong with my choice of terms there, because I keep getting a "data mismatch" error.
I know I can open a table and do a search and replace all fields manually, but I need to have this process entirely automated, and nothing I am doing in code or in macros seems to be working. Can anyone help me?
Thanks

 

[psemianonymous]

How many tables are you doing this on? Are the fields in this (these) tables the same?

You could set an UPDATE query to replace all EOLN markers with empty strings. The SQL would look something like:

[tt]UPDATE table1 SET field1 = Replace([field1],Chr(13),"")
WHERE InStr(1, Nz([field1]), Chr(13)) > 0[/tt]


Something like that. That would update exactly one field in one table, all the way through the table. Make a query for this for each field that you need to update, and then make a macro (or a VBA subroutine) to run every single one of these queries. It's pretty easy to set up, and (I think) elegant.

There is a way in code to loop through every field in every table programmatically, and theoretically you could construct and run an UPDATE query on any set of tables and fields, but I don't think it's worth the hassle. Just make a hundred queries and run them all inside a macro.

The only stern advice I have is to be very conscientiuos(sp?) with your query naming system.

 

[spider8]

Thanks-- I was trying to get around having to write a separate query for each field (I do have a bunch of them now, but I have a bunch more to write, I guess) but if that's still the simplest option, then I guess I'll have to write a whole bunch of queries and then set up a macro to run them all, which I can do--Thanks much for your input

 

[ByteMyzer]

You might try the following code:

Public Sub vbCrLfStrip(table As String)

    Dim dbs As Database
    Dim fld As Field
    Dim strSQL As String

    Set dbs = CurrentDb
    strSQL = "UPDATE [" & table & "] SET "
    For Each fld In dbs.TableDefs(table).Fields
        If fld.Type = dbText Then
            strSQL = strSQL & "[" & fld.Name & "]=" _
                & "Replace(Replace([" & fld.Name & "],Chr(13),""""),Chr(10),""""),"
        End If
    Next
    strSQL = Left(strSQL, Len(strSQL) - 1) & ";"

    dbs.Execute strSQL
    dbs.Close

End Sub

Calling this sub with:
[tt]Call vbCrLfStrip("MyTable")[/tt]

...will strip all Chr(13)'s and Chr(10)'s from all text fields in all records in the table MyTable.

 

[spider8]

Oh, This does look promising--just the type of procedure I've been trying to write but not succeeding at...
I am getting a "Type mistmatch" error on
For Each fld In dbs.TableDefs(table).Fields
But I'll keep trying...