Forum Discussion
Formatting 2-digit year in MS Access. How?
- Hello Gustav,
And that was the way to do it. The option you referred to, I didn't realize that it was existing. Thank you very much.
Regards from the Netherlands.
Keeping in mind that what you are talking about is a FORMAT FOR DISPLAY PURPOSES, and not the actual DATE VALUE STORED, you can do this with a Format() expression wherever you need to DISPLAY that FORMATTED STRING.
Format(Month(Date), "mm") & "/" & Format(Year(Date),"yy")
or
Format(Month(Date), "mm") & "-" & Format(Year(Date),"yy")
Having done this, you are DISPLAYING a string, not a date, so beware of trying to use it for any further date logic.
This is such a common request that I feel it's useful to jabber on about it a bit more.
Access ALWAYS stores dates internally as a full date and time. It does so in the form of a double.
The part of the double to the left of the decimal is the number of elapsed days since the starting date for Access dates, which is December 30, 1899 (or 12/30/1899 in the US format used by Access be default)
The part of the double to the right of the decimal is the elapsed time since midnight of the date specified. For example, 8:00AM is 8 hours after midnight, or 8/24th of a day, or 1/3 of a day, or .3333
So the current date and time might be 44334.3333 representing 8:00 o'clock in the morning on the 18th of May, 2021.
Note that in my example, I actually used several different FORMATS to express dates and times. We, humans, have learned to interpret a number of DATE FORMATS so we just do it without thinking.
The important point is that no matter how the date and time are expressed, the underlying value is what Access actually stores in the table. And it stores that value as I explain above.
So, when working with dates and times in a relational database application, be careful not to confuse the ability to FORMAT a date for DISPLAY with the actual value it represents.