Hi all,
Probably very simple but having an issue where some of my dates in a table are being written as long dates, and some short dates. I thought instead searching and fixing each date modifier, I'll simply write a short piece of code to run at the end of the module to reformat all dates. You'll see from the code below, that all of a sudden all of my KPI figures shot to 100% after the code!... Because it had changed all of the dates to TODAY'S date and therefore passed everything
.
TL;DR: What function in VBA to modify the format to 'short date' within a table?
Current code:
Sub Mean_green_date_formatting_machine()
Dim db As Database
Dim rst As Recordset
Dim cnt As Integer
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("003_so_master")
cnt = rst.RecordCount
rst.MoveFirst
For i = 1 To cnt
rst.Edit
rst("ORDER_DATE") = Format(rst("ORDER_DATE"), Date)
rst("LT_DATE") = Format(rst("LT_DATE"), Date)
rst("REQUESTED_DATE") = Format(rst("REQUESTED_DATE"), Date)
rst("PROMISED_DATE") = Format(rst("PROMISED_DATE"), Date)
If IsNull(rst("despatched_date")) = True Then
GoTo NextCycle
Else
rst("DESPATCHED_DATE") = Format(rst("DESPATCHED_DATE"), Date)
End If
NextCycle:
rst.Update
rst.MoveNext
Next i
rst.Close
End Sub
Probably very simple but having an issue where some of my dates in a table are being written as long dates, and some short dates. I thought instead searching and fixing each date modifier, I'll simply write a short piece of code to run at the end of the module to reformat all dates. You'll see from the code below, that all of a sudden all of my KPI figures shot to 100% after the code!... Because it had changed all of the dates to TODAY'S date and therefore passed everything

TL;DR: What function in VBA to modify the format to 'short date' within a table?
Current code:
Sub Mean_green_date_formatting_machine()
Dim db As Database
Dim rst As Recordset
Dim cnt As Integer
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("003_so_master")
cnt = rst.RecordCount
rst.MoveFirst
For i = 1 To cnt
rst.Edit
rst("ORDER_DATE") = Format(rst("ORDER_DATE"), Date)
rst("LT_DATE") = Format(rst("LT_DATE"), Date)
rst("REQUESTED_DATE") = Format(rst("REQUESTED_DATE"), Date)
rst("PROMISED_DATE") = Format(rst("PROMISED_DATE"), Date)
If IsNull(rst("despatched_date")) = True Then
GoTo NextCycle
Else
rst("DESPATCHED_DATE") = Format(rst("DESPATCHED_DATE"), Date)
End If
NextCycle:
rst.Update
rst.MoveNext
Next i
rst.Close
End Sub