Clean and Format Access Table Values with VBA
Question details
The user wants to clean text and format date and time values throughout an Access table using VBA.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A table contains fields that need different processing, including trimming, uppercase conversion, and date or time formatting.
- Observed behavior
- The goal is to process each appropriate field by name while leaving AutoNumber and other unsuitable fields unchanged.
In Access, open the table in Design View and identify its text fields and native Date/Time fields. Test the procedure on a copy of the table, and check that the date and time text uses formats Access can interpret under your regional settings.
Clean text fields with a DAO recordset
Use this procedure when text fields need trimming and uppercase conversion, and specific text fields contain dates or times that need formatting.
The example treats DateText and TimeText as text field names. Replace them with your actual field names. Native Date/Time fields are not changed; set their display formats separately in Design View.
Only apply date or time conversion to fields whose contents you have verified. IsDate depends on regional settings and can interpret ambiguous text differently than intended. Values that are not recognized as dates, such as NA, are only trimmed and uppercased.
In Access, press Alt+F11, then choose Insert > Module in the VBA editor. Confirm that a Microsoft Office Access database engine object library reference is available under Tools > References.
Paste this code into the module, replacing DateText and TimeText with the names of your date-text and time-text fields: Public Sub UpdateTable(ByVal tableName As String) Dim db As DAO.Database Dim rs As DAO.Recordset Dim fld As DAO.Field Dim valueText As String Dim changed As Boolean Set db = CurrentDb Set rs = db.OpenRecordset(tableName, dbOpenDynaset) Do While Not rs.EOF rs.Edit changed = False For Each fld In rs.Fields If (fld.Type = dbText Or fld.Type = dbMemo) _ And (fld.Attributes And dbAutoIncrField) = 0 Then If Not IsNull(rs.Fields(fld.Name).Value) Then valueText = UCase$(Trim$(CStr(rs.Fields(fld.Name).Value))) Select Case fld.Name Case "DateText" If IsDate(valueText) Then valueText = Format$(CDate(valueText), "yyyy\/mm\/dd") End If Case "TimeText" If IsDate(valueText) Then valueText = Format$(CDate(valueText), "hh\:nn\:ss") End If End Select If valueText <> CStr(rs.Fields(fld.Name).Value) Then rs.Fields(fld.Name).Value = valueText changed = True End If End If End If Next fld If changed Then rs.Update Else rs.CancelUpdate End If rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing End Sub
Press Ctrl+G to open the Immediate window and enter UpdateTable "TableName", replacing TableName with the name of an editable test table. Open that table in Datasheet View and inspect the results.

Set display formats for native Date/Time fields
Use field properties when dates or times are stored as Access Date/Time values rather than text.
Work with Office documents in WPS Office
WPS Office is a free, lightweight alternative for everyday document, spreadsheet, and presentation work. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files into your workflow. It does not run Access databases or fix Access VBA procedures.

Frequently Asked Questions
Why should the code use Fields(fld.Name) instead of Fields(1)?
Fields(fld.Name) refers to the field currently being processed in the loop. Fields(1) always refers to the same field, so it can update the wrong value repeatedly.
Will the procedure turn NA into a date?
IsDate("NA") returns False, so the example only trims and uppercases that value. If other values are unexpectedly converted, check the field names, regional settings, and nesting of the If and Select Case statements.
How do I run the procedure for a table?
After adding the procedure to a standard module, press Ctrl+G in the VBA editor and enter UpdateTable "TableName" in the Immediate window, substituting your table name.
Why does a Date/Time field need a Format property?
Access stores a native Date/Time value rather than a formatted date string. In table Design View, select the field and set its Format property to control how it appears.
Can I pass a table name from an Access form?
Yes. Add an unbound text box named txtTableName to the form, then call UpdateTable Me.txtTableName from a form event or button procedure. Verify the entered table name before running it.




