Update all fields in table (1 Viewer)

phinix · Nov 25, 2019

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....

Code:

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 · Nov 25, 2019

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

phinix · Nov 25, 2019

theDBguy said:
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 · Nov 25, 2019

update on a field by field basis - something like

Code:

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

theDBguy · Nov 25, 2019

phinix said:
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 · Nov 25, 2019

CJ_London said:
update on a field by field basis - something like

Code:

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 · Nov 25, 2019

theDBguy said:
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 · Nov 25, 2019

phinix said:
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 · Nov 25, 2019

theDBguy said:
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 · Nov 25, 2019

phinix said:
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:

Code:

If fld.Name <> "ID" Then

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

phinix · Nov 25, 2019

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

Code:

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 · Nov 25, 2019

phinix said:
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 · Nov 25, 2019

theDBguy said:
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:

Code:

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!

Code:

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

Mark_ · Nov 25, 2019

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 · Nov 25, 2019

phinix said:
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:

Code:

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!

Code:

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

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

Micron · Nov 25, 2019

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

Update all fields in table (1 Viewer)

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

CJ_London

Super Moderator

theDBguy

I’m here to help

phinix

Registered User.

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

Mark_

Longboard on the internet

theDBguy

I’m here to help

Micron

AWF VIP

Similar threads

Users who are viewing this thread