---

MS Access 2003: Update a date field with VBA code

This MSAccess tutorial explains how to update a date field using VBA code in Access 2003 (with step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have a database which contains a date field called TransactionDate. I want to be able to query the user for a new date and then update the TransactionDate field based on what the user entered.

Answer: You can update your date field using a custom function.

Open your Access database, click on the Modules tab and create a new Module. Paste in the following code:

Function UpdateTransactionDate() As Boolean

   Dim db As Database
   Dim LUpdate As String
   Dim LMsg As String
   Dim LTransactionDt As Date

   On Error GoTo Err_Execute

   'Query user for Date of R
   LMsg = "Enter the Date of R Form __/__/____"
   LMsg = LMsg & Chr(10) & Chr(10) & "Format date as: mm/dd/yyyy"

   LTransactionDt = InputBox(LMsg)

   Set db = CurrentDb()

   'Re-Assign Date to TransactionDate
   LUpdate = "update [ChapterFormBulkInputTable]"
   LUpdate = LUpdate & " set [TransactionDate] = #" & Format(LTransactionDt, "mm/dd/yyyy") & "#"

   db.Execute LUpdate, dbFailOnError

   Set db = Nothing

   MsgBox "Changing the Dates was Successful."
   UpdateTransactionDate = True

   On Error GoTo 0

   Exit Function

Err_Execute:
   MsgBox "Updating the dates failed, you will need to enter each date individually."
   UpdateTransactionDate = False

End Function

You can then call the function from a Form (ie: button click event, etc) or a Macro and it will update the TransactionDate field.

If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.