Update all fields in table

[phinix]

I'm trying to run an update query on all fields in a table.
Like for example, replace all Chr(10) or Chr(13) with a coma.
It also needs to skip the ID field, as it is an autonumber.

How can I do this?

Here's my code, but it doesn't work....

Public Function GetRidOf_dollar() As Boolean
  'field all rows of all fields in a table
  
 Dim fld As DAO.Field
Dim db As DAO.Database
Dim strSQL As String
Dim tbl As String
  
  
  Set db = CurrentDb
  tbl = "ProcessTable"

  'loop through fields
   For Each fld In db.TableDefs(tbl).Fields
    'create an update SQL statement for each field
    If fld.Name <> ID Then
        strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Replace([" & fld.Name & "],Chr(10),',')"
    'run the query
    CurrentDb.Execute strSQL, dbFailOnError 'roll back if error occurs
  End If
  Next fld
      
End Function

 

[theDBguy]

Hi. How many fields are we talking about? Are you asking how to do this in code or one query?

 

[phinix]

Hi. How many fields are we talking about? Are you asking how to do this in code or one query?

I added my code above.
I want it to run on all fields.

 

[CJ_London]

update on a field by field basis - something like

UPDATE myTable 
SET myField1 = Replace([myField1],Chr(10) & Chr(13),","), 
      myField2 = Replace([myField2],Chr(10) & Chr(13),",")

 

[theDBguy]

I added my code above.
I want it to run on all fields.

Hi. I see it now. What does "doesn't work" mean? Were you getting any errors? If so, what was the error message?

 

[phinix]

update on a field by field basis - something like

UPDATE myTable 
SET myField1 = Replace([myField1],Chr(10) & Chr(13),","), 
      myField2 = Replace([myField2],Chr(10) & Chr(13),",")

Yes, but number of fields are unknown, so I want it to run for each field, but skip ID.

 

[phinix]

Hi. I see it now. What does "doesn't work" mean? Were you getting any errors? If so, what was the error message?

Error: Cannot update 'ID'; field not updateable.

I've put in condition but for some reason it doesn't work.

 

[theDBguy]

Error: Cannot update 'ID'; field not updateable.

I've put in condition but for some reason it doesn't work.

What does your condition look like?

 

[phinix]

What does your condition look like?

In that code:

For Each fld In db.TableDefs(tbl).Fields
'create an update SQL statement for each field
If fld.Name <> ID Then
strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Replace([" & fld.Name & "],Chr(10),',')"
'run the query
CurrentDb.Execute strSQL, dbFailOnError 'roll back if error occurs
End If
Next fld

 

[theDBguy]

In that code:

For Each fld In db.TableDefs(tbl).Fields
'create an update SQL statement for each field
If fld.Name <> ID Then
strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Replace([" & fld.Name & "],Chr(10),',')"
'run the query
CurrentDb.Execute strSQL, dbFailOnError 'roll back if error occurs
End If
Next fld

Hi. Thanks for the additional information. I think that should have been:

If fld.Name <> "ID" Then

However, that assumes all "ID" fields are named "ID", which may or many not be the case.

 

[phinix]

Hi. Thanks for the additional information. I think that should have been:

If fld.Name <> "ID" Then

However, that assumes all "ID" fields are named "ID", which may or many not be the case.

Yep, it worked thank you!
Can't believe it was all about quotes...

 

[theDBguy]

Yep, it worked thank you!
Can't believe it was all about quotes...

Hi. Glad to hear it worked for you, but you might also consider what I said about relying on a specific field name. Good luck with your project.

 

[phinix]

Hi. Glad to hear it worked for you, but you might also consider what I said about relying on a specific field name. Good luck with your project.

Thanks

I have another issue there.
Same code, updating all fields in table that are "" or " " or " "
It happens when I import tables from Excel, some fields are not null, but empty, or have one or two spaces.

So I have this:

Public Function GetRidOf_empty() As Boolean
  
Dim fld As DAO.Field
Dim db As DAO.Database
Dim strSQL As String
Dim tbl As String
  
  Set db = CurrentDb
  tbl = "ProcessTable"

   For Each fld In db.TableDefs(tbl).Fields
     If fld.Name <> "ID" Then
        strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Null WHERE " & fld.Name & " = '' or " & fld.Name & " = ' ' or " & fld.Name & " = '  '"
      CurrentDb.Execute strSQL
        End If
  Next fld
  
     
End Function

When I run it, I get error: Data type mismatch in criteria expression

Would that be because it runs on a field that is a number for example?
How can I make it foolproof, so it will skip other fields than text?


EDIT:

Got it!

If fld.Name <> "ID" And fld.Type = dbText Then

 

[Mark_]

I believe you want to use the TRIM function to remove excess spaces.

Looking at it, you may want to build your SQL string one field at a time then do ONE update on a record, if needed.

 

[theDBguy]

Thanks

I have another issue there.
Same code, updating all fields in table that are "" or " " or " "
It happens when I import tables from Excel, some fields are not null, but empty, or have one or two spaces.

So I have this:

Public Function GetRidOf_empty() As Boolean
  
Dim fld As DAO.Field
Dim db As DAO.Database
Dim strSQL As String
Dim tbl As String
  
  Set db = CurrentDb
  tbl = "ProcessTable"

   For Each fld In db.TableDefs(tbl).Fields
     If fld.Name <> "ID" Then
        strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Null WHERE " & fld.Name & " = '' or " & fld.Name & " = ' ' or " & fld.Name & " = '  '"
      CurrentDb.Execute strSQL
        End If
  Next fld
  
     
End Function

When I run it, I get error: Data type mismatch in criteria expression

Would that be because it runs on a field that is a number for example?
How can I make it foolproof, so it will skip other fields than text?


EDIT:

Got it!

If fld.Name <> "ID" And fld.Type = dbText Then

Hi. Congratulations! Glad to hear you got it sorted out. Cheers!

 

[Deleted member 147267]

AFAIK, a field name cannot contain characters like = so that might be it. Would help immensely if everyone always specified which line raises the error.

Your sql statement is saying WHERE field name = '' and this cannot be. Remove the .Name part from the Where clause and see what you get.

BTW - I'd advise that you use more descriptive variable names (include a preface for the type, such as strTbl). It will make your code much clearer to you and those that try to follow it. If you need to troubleshoot 6 months from now, it's far easier to realize that strTbl is a string. Just tbl could also infer that it's an actual object. See
https://access-programmers.co.uk/forums/showthread.php?t=225837
and
http://access.mvps.org/access/general/gen0012.htm