Solved VBA to change format of records in a table (1 Viewer)

Javalon

New member
Local time
Today, 19:51
Joined
Nov 10, 2021
Messages
16
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 :p.

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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:51
Joined
Feb 19, 2013
Messages
16,553
What function in VBA to modify the format to 'short date' within a table?
are your fields a date datatype or text?

If the former, you don't code, just set the format property of the field to whatever you want

If the latter, why store as text?, but if for some reason this is a requirement, this is how you use to format function

in summary

format(myfield,"short date")
?format("25/12/2004","short date")
25/12/2004

or perhaps format(myfield,"mm/dd/yyyy")
?format("25/12/2004","mm/dd/yyyy")
12/25/2004

short date will format per the format specified by your windows settings

for the future, when posting code, please use the code tags to preserve formatting- highlight your code then click the </> button
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:51
Joined
May 7, 2009
Messages
19,169
unfortunate, doing:

Format(rst("ORDER_DATE"), Date)

is wrong!

and the bad news, if you don't have backup of your table, there is no way
to recover the previous date (if there is any date to it).

just leave the format "as-is", you can then "format" if using Forms.
 

Eugene-LS

Registered User.
Local time
Today, 22:51
Joined
Dec 7, 2018
Messages
481
What function in VBA to modify the format to 'short date' within a table?
Try code:
Code:
CurrentDb.TableDefs("Your Table Name").Fields("Your DateTime FIELD").Properties("Format") = "Short Date"
 

Javalon

New member
Local time
Today, 19:51
Joined
Nov 10, 2021
Messages
16
and the bad news, if you don't have backup of your table, there is no way
to recover the previous date (if there is any date to it).
Haha! Don't you worry, I've been doing this long enough to know to keep backups! In fact, I actually pull the data from a CSV and entirely rebuild the table into a static. So the original data is still preserved in CSV (y)
 

Javalon

New member
Local time
Today, 19:51
Joined
Nov 10, 2021
Messages
16
...

for the future, when posting code, please use the code tags to preserve formatting- highlight your code then click the </> button

I'll give these a go now, thanks for the reply!

AND thanks for the tip on posting code, I've not really used forums for programming before so didn't realize it was a feature, thanks (y)
 

Javalon

New member
Local time
Today, 19:51
Joined
Nov 10, 2021
Messages
16
Try code:
Code:
CurrentDb.TableDefs("Your Table Name").Fields("Your DateTime FIELD").Properties("Format") = "Short Date"
It didn't like this, threw up error 3270, Property Not Found.
Thanks anyway
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:51
Joined
May 7, 2009
Messages
19,169
don't worry too much of the format of field in the table.
worry about the format in the csv file.
 

Javalon

New member
Local time
Today, 19:51
Joined
Nov 10, 2021
Messages
16
...

format(myfield,"short date")
?format("25/12/2004","short date")
25/12/2004

...
This worked! Thanks!
I do have the field set to short date, which is why it was confusing me. I have a multitude of functions and queries that modify the original dates based on various factors, some of which were apparently writing the long dates. Hence my headscratching and eventual idea to just reformat the lot at the end.

Anyway, this works! So all is good, thanks :cool:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:51
Joined
May 7, 2009
Messages
19,169
right-click your table on Navigation pane and select Design View from the shortcut menu.
below panel there is Format, just type "short date" (without quote). save your table.
 

Javalon

New member
Local time
Today, 19:51
Joined
Nov 10, 2021
Messages
16
Dammit Eugene, ya got me!

How would I go about adding that property?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:51
Joined
Feb 19, 2002
Messages
42,973
If your dates are defined as datetime - you NEVER answered that question - then the problem is that some of the fields contain time and others do not. To solve this problem, you need to remove the time part of the data AND you need to find the code that is using Now() and replace it with Date().

And finally, NEVER format data at the table level. All it does is obfuscate what is actually being stored. When your date fields randomly contain time values, no matter what formatting you apply, the time part is STILL THERE and will cause errors with compares because #3/16/2022 10:47 AM# is GREATER THAN #3/16/2022#, NOT equal.

PS, you would never loop through a recordset and update the format for each record. There is only ONE format property for the field. It is not like Excel where you have a different format for each cell.
 

Users who are viewing this thread

Top Bottom