Run update query on all fields 1

[judgehopkins]

On an imported database, I want to trim all the spaces in the table.

UPDATE tblMyTable
SET [fldMyField] = Trim([fldMyField])
WHERE [fldMyField] <> Trim([fldMyField])

Question: I know I can use the above SQL code to update one field at a time, but how do I build the update query where it does this to every field?

(All the fields are in one table.)

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[GingerR]

UPDATE tblMyTable
SET tblMyTable.fldMyField1 = Trim([fldMyField1]), tblMyTable
.fldMyField2 = Trim([fldMyField2]), tblMyTable.fldMyField3 = Trim([fldMyField3]);

 

[judgehopkins]

I found this nifty procedure in thread700-408588 but I wondered if it was possible to do the same thing in SQL on an update query.

Public Function TrimAllFields(TableName As String) As Boolean
  'field all rows of all fields in a table
  
  Dim fld As DAO.Field
  Dim strSQL As String
  Dim db As Database
  
  on error goto errUpdate
  
  'show busy
  DoCmd.Hourglass True
  
  Set db = CurrentDb

  'loop through fields
  For Each fld In db.TableDefs(TableName).Fields
    'create an update SQL statement for each field
    strSQL = "UPDATE [" & TableName & "] SET [" & TableName & "].[" & fld.Name & "] = Trim([" & fld.Name & "])"
    'run the query
    CurrentDb.Execute strSQL, dbFailOnError 'roll back if error occurs
  Next fld
  
  TrimAllFields = True  'it worked
  
CleanUp:
  DoCmd.Hourglass False
  Set fld = Nothing
  Exit Function

errUpdate:
  MsgBox "Error occured triming fields in " & TableName, vbExclamation, "Error"
  Resume CleanUp
  
End Function

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[GingerR]

not sure now what your question is?
i gave an example of how to write an update query, and you have a good bit of code that looks like it does the exact same thing. have you used either? did it work for you? are you still having problems?

 

[judgehopkins]

Thanks, GingerR!

(1) Your way. Yes, I already knew how to do that and I'm sorry I did not make it clear. My SQL is EQL to NULL. I was wondering how to run an update query with a wildcard.

(B) The code. Excellent code from that thread. I can tweak it to where it will do the same thing as running an update query made with SQL.

I guess it really doesn't make much of a difference, does it? I was looking for the simplest and easiest way that works! (Aren't we all....)

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[mpastore]

Judge,

Try this, it will take field1 and show it and its translated value, you may have to account for nulls, etc. If it gives you what you want just make an update query out of it. I'm making first char upper, not sure how to make all proper in one sql statement:

SELECT Table1.field1, ucase(mid(trim(field1),1,1)) & mid(TRIM(trim(Replace([field1],&quot; &quot;,&quot;&quot)),2) AS Expr1
FROM Table1;


Mike Pastore

Hats off to (Roy) Harper

 

[judgehopkins]

When I use the code below in an update query, it creates a query that does not change words in all caps to words with first letter capitalized and it also creates a new column named &quot;Expr1&quot; with the same data that is in BuyerOwnerLN. And it does not change the underlying data in the table.

Hmmm...what could the matter be?

SELECT OldData.BuyerOwnerLN, ucase(mid(trim(BuyerOwnerLN),1,1))  &  mid(TRIM(trim(Replace([BuyerOwnerLN],"  ",""))),2) AS Expr1
FROM OldData;

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[mpastore]

You would need to make an update query:

update olddate set buyerOwnerLN = ucase(mid(trim(BuyerOwnerLN),1,1)) & mid(TRIM(trim(Replace([BuyerOwnerLN],&quot; &quot;,&quot;&quot)),2)

However this will not change all your words to proper (only first) so I'm not sure it's what you need anyway. Unless there is a proper() function in VBA, you will have to write a custom function to do this.

Mike Pastore

Hats off to (Roy) Harper

 

[judgehopkins]

Thanks, Mike!

I will try this and let you know how I fare....

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[judgehopkins]

One more thing...is it possible to write the query so that it programmatically does the thing to all fields in the table instead of me having to type in the procedure for all fields? (I.e., similar to the code I posted above.)

Access has a vbProperCase, but it does not work in queries...or, at least, I cannot get it to work there!



&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[mpastore]

Judge,

I know that a function can be called from a query but I don't see how that function could act on all fields at one time unless they are explicity passed as parameters. The best I could see is that you use VBA to acomplish this task with the field collection of the table (like one of your prior postings illustrates).

You could also write a function that creates a query dynamically with the syntax that you would have had to do manually. This also falls along the VBA line much like your earlier posting.

Mike Pastore

Hats off to (Roy) Harper

 

[judgehopkins]

When I tried using that function, I get an error that says that Access expects a procedure not a function. (I checked my references and had to add Microsoft DAO 3.6.)

So, my questions now are: Where exactly do I put that function and how do I call it? (I mean, I know I will have to do it in the code editor, etc. I just cannot get the thing to work!)

BTW, thank you very much for the time and attention you have devoted to this problem!

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[mpastore]

Hey Judge,

Here's a proper function:


Function getProper(ps As String) As String
'?getProper(&quot;MICHAEL ANDERSON 122 SPRAGUE ST&quot
If IsNull(ps) Then
getProper = &quot;&quot;
Exit Function
End If

Dim sRet As String
sRet = LCase(ps)
sRet = UCase(Left(sRet, 1)) & Mid(sRet, 2) ' first letter upper

Dim iWhere As Integer
Dim iStart As Integer
iStart = 1
iWhere = InStr(iStart, sRet, &quot; &quot
Do While iWhere > 0
sRet = Left(sRet, iWhere) _
& UCase(Mid(sRet, iWhere + 1, 1)) _
& Mid(sRet, iWhere + 2)

iStart = iWhere + 1
iWhere = InStr(iStart, sRet, &quot; &quot
Loop

getProper = sRet

End Function


Now your update might be:

update olddate set buyerOwnerLN = getProper(ucase(mid(trim(BuyerOwnerLN),1,1)) & mid(TRIM(trim(Replace([BuyerOwnerLN],&quot; &quot;,&quot;&quot)),2))

Mike Pastore

Hats off to (Roy) Harper

 

[mpastore]

Judge,

Check out this thread: 700-585439

Of course there was an easier way!!

StrConv([myfield], vbProperCase)

Mike Pastore

Hats off to (Roy) Harper

 

[judgehopkins]

Mike, I get error messages when I use the strconv, etc. line in an update query.

Is there any way I can do this in VB using the code I posted earlier?

I just cannot figure out how to get that to run!

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[mpastore]

Try using the proper function I gave you (note, I am not addressing the updating of all fields in table).

Mike Pastore

Hats off to (Roy) Harper

 

[judgehopkins]

Hey, Mike!

I pasted your code and saved it as getProper and compiled.

When I tried this line

?getProper("MICHAEL ANDERSON 122 SPRAGUE ST")

in the immediate window, I got this error message:

Compile error: expected variable or procedure, not function

I then made an update query and used this code:

update olddata set buyerOwnerLN = getProper(ucase(mid(trim(BuyerOwnerLN),1,1))  &  mid(TRIM(trim(Replace([BuyerOwnerLN],"  ",""))),2))

I got this error message:

Undefined function 'getProper' in expression


What now?

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[mpastore]

Stick with the immediate window issue for now.

Create a new module like basFunc and paste the code in there.

Press CTL+G to bring up the immediate window.

Paste:

?getProper(&quot;MICHAEL ANDERSON 122 SPRAGUE ST&quot

into immediate window and press enter. What happens? Also, what version of MS Access are you running?

Mike Pastore

Hats off to (Roy) Harper

 

[judgehopkins]

Mike, I am using Access 2000. I will experiment some more and get back with you.

I posted a similar question here: thread220-587983

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[judgehopkins]

You said: &quot;Paste:

?getProper(&quot;MICHAEL ANDERSON 122 SPRAGUE ST&quot

into immediate window and press enter. What happens?&quot;

I got this error message:

Compile error: expected variable or procedure, not function



&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air