Forum Discussion
formatting date where you don't live in USA
Dear VBA/Access Guy when you leave outside of usa formatting date can be different . In USA the date are formatting like "mm/dd/yyyy". here in belgium, but for what I know same in France, Netherl...
Hi Anthony,
Microsoft Access actually automatically displays dates in the dd/mm/yyyy format outside of U.S.
However this is based on the region you have selected or overriding date format you have selected in Microsoft Windows Regional and Language Options setting. I would suggest I get checking and changing, if needed, those settings for your PC.
That said, you can override that I'd you are sure that users of your database will almost always want dates in International (non-US) format.
In Design View for Tables you can select a Date/Time field and go to Field Properties > Format. There, as shown in the attached screenshot, you can select General Date, Long Date, etc predefined formats and preview how they would look based on your PC's locale settings.
You can also enter a custom display format to always use such as the following for international format:
m/dd/yyyy
Or if you want time as well then:
m/dd/yyyy h:mm:ss
You can also use such formats with the Format function in VBA or even Query Expressions and Calculated Fields:
In VBA that would be:
Format(varDate, "\#dd\/mm\/yyyy\#")
In SQL or Query Designer that would be:
Format(dateField, "/mm/dd/yyyy")
However, if you want to specify a hardcoded date literal in query criteria or VBA using ## notation (efficiently, with parsing from a strong, though that's an option too) then you should always use US date format. However that isn't displayed to your end users. With criteria in Query Designer such as:
> #2/2/2012#
With more examples including various date functions you can use in Queries and VBA here:
https://support.office.com/en-us/article/examples-of-using-dates-as-criteria-in-access-queries-aea83b3b-46eb-43dd-8689-5fc961f21762
Here is an excerpt of relevant documentation on how PC regional settings are used:
Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. were mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds but you can display the date and time in several formats.
These automatic formats for dates and times vary depending on the geographic location specified in the Microsoft Windows Regional and Language Options setting on your computer. For example, in Europe and many parts of Asia, depending on your location, you might see the date and time as 28.11.2018 12:07:12 PM or 28/11/2018 12:07:12 PM. In the United States, you would see 11/28/2018 12:07:12 PM. It is possible to change these automatic formats by using custom display formats. The display format that you select will not affect how the data is entered or how Access stores that data. For example, you can enter a date in a European format such as 28.11.2018, and have the table, form, or report display the value as 11/28/2018.
See more about setting date formats in fields and query results, along with documentation about how your Windows regional settings are used for the default date format, here:
https://support.office.com/en-us/article/format-the-date-and-time-field-in-access-47fbbdc1-52fa-416a-b8d5-ba24d881b698
Microsoft Access actually automatically displays dates in the dd/mm/yyyy format outside of U.S.
However this is based on the region you have selected or overriding date format you have selected in Microsoft Windows Regional and Language Options setting. I would suggest I get checking and changing, if needed, those settings for your PC.
That said, you can override that I'd you are sure that users of your database will almost always want dates in International (non-US) format.
In Design View for Tables you can select a Date/Time field and go to Field Properties > Format. There, as shown in the attached screenshot, you can select General Date, Long Date, etc predefined formats and preview how they would look based on your PC's locale settings.
You can also enter a custom display format to always use such as the following for international format:
m/dd/yyyy
Or if you want time as well then:
m/dd/yyyy h:mm:ss
You can also use such formats with the Format function in VBA or even Query Expressions and Calculated Fields:
In VBA that would be:
Format(varDate, "\#dd\/mm\/yyyy\#")
In SQL or Query Designer that would be:
Format(dateField, "/mm/dd/yyyy")
However, if you want to specify a hardcoded date literal in query criteria or VBA using ## notation (efficiently, with parsing from a strong, though that's an option too) then you should always use US date format. However that isn't displayed to your end users. With criteria in Query Designer such as:
> #2/2/2012#
With more examples including various date functions you can use in Queries and VBA here:
https://support.office.com/en-us/article/examples-of-using-dates-as-criteria-in-access-queries-aea83b3b-46eb-43dd-8689-5fc961f21762
Here is an excerpt of relevant documentation on how PC regional settings are used:
Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. were mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds but you can display the date and time in several formats.
These automatic formats for dates and times vary depending on the geographic location specified in the Microsoft Windows Regional and Language Options setting on your computer. For example, in Europe and many parts of Asia, depending on your location, you might see the date and time as 28.11.2018 12:07:12 PM or 28/11/2018 12:07:12 PM. In the United States, you would see 11/28/2018 12:07:12 PM. It is possible to change these automatic formats by using custom display formats. The display format that you select will not affect how the data is entered or how Access stores that data. For example, you can enter a date in a European format such as 28.11.2018, and have the table, form, or report display the value as 11/28/2018.
See more about setting date formats in fields and query results, along with documentation about how your Windows regional settings are used for the default date format, here:
https://support.office.com/en-us/article/format-the-date-and-time-field-in-access-47fbbdc1-52fa-416a-b8d5-ba24d881b698