Change date format to display dots instead of forward slashes? (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 14:47
Joined
Nov 23, 2019
Messages
75
Hi all, I've come across a problem with the database I am working on, there are a few things I need to suss out but I want to get this out of the way first

I'm posting this in the tables section as I guess if it can be done in a table then it can be transferred to forms & reports etc, I want to use this as part of the naming for a pdf report which can't be done with "/" separating each part of the date.

Basically I would like the date added like 04.03.2020 rather than 04/03/2020

Is it possible by using code or changing settings in the database I am working on?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:47
Joined
May 21, 2018
Messages
8,463
format(yourdate,"mm.dd.yyyy")
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:47
Joined
Aug 30, 2003
Messages
36,118
It can be done with the Format() function:

?format(date(),"dd.mm.yy")
04.03.20
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:47
Joined
Aug 30, 2003
Messages
36,118
They're the same technique. MajP used US format, I followed yours. You can use that pretty much anywhere, though I don't know about in a table. I never format in the table so not sure on that.
 

PaulD2019

Registered User.
Local time
Today, 14:47
Joined
Nov 23, 2019
Messages
75
Thanks again @pbaldy

I wouldn't normally format a table field like that either but I was guessing for the code I'm using to auto-name the file with different fields it needs to be done from the table, I was only guessing that.

I'll give it a try tomorrow
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:47
Joined
Aug 30, 2003
Messages
36,118
If it does, I just tested and this in the format property of a field appears to produce the result you want:

dd\.mm\.yy
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
Note that if your default Windows date settings are dd.mm.yyyy then dates will automatically be displayed in that format in Access
That is of course UNLESS you apply different formatting in a particular control
 

PaulD2019

Registered User.
Local time
Today, 14:47
Joined
Nov 23, 2019
Messages
75
Sorry, not been online for a few days. Thank you both for your replies
If it does, I just tested and this in the format property of a field appears to produce the result you want:

dd\.mm\.yy
I'm still not 100% sure how to add what you have said above (still learning :rolleyes:)

Note that if your default Windows date settings are dd.mm.yyyy then dates will automatically be displayed in that format in Access
That is of course UNLESS you apply different formatting in a particular control
I was wondering if it had something to do with that, I think I had the same sort of issue in the past, a control for the formatting might be what I need then.


I need to add the date that is in the TestDate field from the table I have created part of the form from to be included in the file name when the report is saved as a .pdf file in a folder.
Currently I have the below code to save the file as a .pdf with part of the file name but without the date currently being added to the file name

Code:
Private Sub btnSavePATTestCert_Click()
Dim FileName As String
Dim FilePath As String

FileName = "Test Certificate" & Me.CertificateNumber & " - " & Me.PlantSerialID & " - " & Me.PlantType
FilePath = CurrentProject.Path & FileName & ".pdf"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OutputTo acOutputReport, "rptTestCert", acFormatPDF, FilePath, acViewPreview
MsgBox FileName & " has been saved successfully to " & FilePath, vbInformation, "Save Confirmed"
End Sub

The code works fine as it is until I try to add the date of the field TestDate in

Is there a way the code could be modified to format the date of the TestDate field so it can be added in the file name?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:47
Joined
Aug 30, 2003
Messages
36,118
To add it to your name:

FileName = "Test Certificate" & Me.CertificateNumber & " - " & Me.PlantSerialID & " - " & Me.PlantType & " - " & Format(Me.TestDate,"dd.mm.yy")
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:47
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,970
I strongly recommend that you never format data at the table level. Add this to your list of defensive programming techniques.

I once spent the better part a day tracking down an intermittent problem with date processing. whenever I looked at the data in the table, all I ever saw was a date. However, the format of a field does not in any way constrain the contents of a field. That is constrained only by its data type. The date data type can include time as well as date and it turns out that in some places, the original programmer was using Now() rather than Date() and so was sometimes putting a time value in the record. Now when I run into strange problems with queries, I start by looking at the format property in the table so I can determine if I am seeing actual values or not.
 

Users who are viewing this thread

Top Bottom