Make first letter in every table uppercae

[hendrikbez]

I have this code in my query to update every first letter in table to upercase, it is working.

UPDATE Blokkies 
SET 
blokkies.Word = UCase(Mid(Trim([Word]),1,1)) & 
Mid(Trim([Word]),2), 
blokkies.home = UCase(Mid(Trim([home]),1,1)) & 
Mid(Trim([home]),2);

I have tried to 2 two of them, but it gives me error "Character found after end of SQL statement"


I know it could be this ."Mid(Trim([home]),2);"

UPDATE Blokkies 
SET 
blokkies.Word = UCase(Mid(Trim([Word]),1,1)) & 
Mid(Trim([Word]),2), 
blokkies.home = UCase(Mid(Trim([home]),1,1)) & 
Mid(Trim([home]),2);

UPDATE Afkortings 
SET 
Afkortings.Woord = UCase(Mid(Trim([Woord]),1,1)) & 
Mid(Trim([Woord]),2), 
Afkortings.Afkorting = UCase(Mid(Trim([Afkorting]),1,1)) & 
Mid(Trim([Afkorting]),2);

Is there a way to do more than one update to diffrent tables, as I have a lot of tables to do this every day.

[June7]

Can update only one table in an update action.

Can't have multiple actions in one SQL.

Need to automate with macro or VBA.

[hendrikbez]

June7

Thank you, will do automate in vb and make a query for each one

[orange]

Hendrik,

This should do what you ask.
Suggest you
-make a copy/backup of your database and
-test this routine on a test database before using with operational database.

' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 18-Sep-19
' ----------------------------------------------------------------
Sub doUpper()
10        On Error GoTo doUpper_Error
          Dim db As DAO.Database
          Dim tdef As DAO.TableDef
20        Set db = CurrentDb
30        For Each tdef In db.TableDefs
40            If Not (tdef.Name Like "Msys*" Or _
                      tdef.Name Like "~*") Then
50                Debug.Print "old :" & tdef.Name
60                tdef.Name = UCase(Left(tdef.Name, 1)) & Mid(tdef.Name, 2)
70                Debug.Print "   new:" & tdef.Name
80            End If
90        Next tdef
          
100       On Error GoTo 0
110       Exit Sub

doUpper_Error:

120       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure doUpper, line " & Erl & "."

End Sub

[hendrikbez]

Thank you Orange