VBA & Macro Problems

Clean and Format Access Table Values with VBA

Kushani NimanthikaKushani Nimanthika Nov 22, 2026 868 views

Question details

The user wants to clean text and format date and time values throughout an Access table using VBA.

Clean and Format Access Table Values with 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.
Before you start

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.

Solution 1Recommended

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.

1
Create a standard module

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.

2
Add the procedure

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

3
Run against a test table

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.

Clean text fields with a DAO recordset
Check field editability: Calculated or read-only text fields, validation rules, unique indexes, and linked-table restrictions can prevent updates. Adjust the procedure to exclude unsuitable fields before running it on your working table.
Free Microsoft Office alternative

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.

Use spreadsheet tools for data cleaning outside an Access databaseOpen and save common Microsoft Office formats, including XLSX, DOCX, and PPTXNavigate a familiar interface when moving from Microsoft OfficeUse a free, lightweight office suite for everyday tasks
microsoft office alternative - wps office

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.