impossible to get year in format date

[Pat Hartman]

Wow, that must be confusing. I've never worked with a foreign language version of Access.

@sonic8 did you say that the SQL View and the QBE View of a query in a non-English version would show different values? How does that work when you are creating SQL using VBA? Your dates need to be either the US format of mm/dd/yyyy or the unambiguous yyyy/mm/dd. Does that mean that you need to create the embedded SQL using the English variables but when you create it using QBE, you use the French variables?

 

[Grumm]

Wow, that must be confusing. I've never worked with a foreign language version of Access.

@sonic8 did you say that the SQL View and the QBE View of a query in a non-English version would show different values? How does that work when you are creating SQL using VBA? Your dates need to be either the US format of mm/dd/yyyy or the unambiguous yyyy/mm/dd. Does that mean that you need to create the embedded SQL using the English variables but when you create it using QBE, you use the French variables?

As far as I created VBA in access on a dutch version of Office, it doesn't matter.

SELECT DateofSale, Year([DateofSale]) AS Expr1 FROM ProductSales;

This select will be the same.

But it looks like even Microsoft doesn't really know what to use in the format function :

Mettre en forme un champ de date et d’heure - Support Microsoft

Lorsque vous utilisez le type de données Date/Heure dans Microsoft Access, vous pouvez conserver les formats par défaut, appliquer un format prédéfini ou créer un format personnalisé.

support.microsoft.com

 

[Galaxiom]

I remember years ago helping a Greek Access user with a similar problem where the first character of the Greek word for month (μήνας) had to be used. Also note that localities where the comma is the regional decimal separator often use semicolons for separating parameters in functions.

Consequently it would suggest that it is necessary that code written in a locality needs to be compiled in that locality too.

The details of the date separator is also need to be understood. The forward slash used as the date separator in the date Format string is not a literal forward slash but a placeholder for the local date separator. Regions with a different date separator such as a dash will substitute that instead and cause problems for the SQL.

The format string for international use should either be "#yyyy-mm-dd#" or "#mm\/dd\/yyyy#". The backslash specifies the next character is literal, rather than the regional date separator.

To add potential confusion, Windows (not just VBA or ACE) will attempt to interpret a date any way that works. Consequently a date Formatted with an unexpected separator in a another region might be wrongly interpreted but get past testing.

My favorite dates are those like 29/2/22 which Windows will quietly interpret as 2029-02-22 since 2022 is not a leap year.

 

[Pat Hartman]

@Grumm But the question is - How do you find out WHAT letters need to be used in the Format() function for different languages? When you google Function() from the Netherlands, do you see different Microsoft documentation than English speakers do?

 

[cheekybuddha]

What's strange is that the french help still uses the english letters:

Fonction format​

 

[Galaxiom]

How do you find out WHAT letters need to be used in the Format() function for different languages?

Try the English and if that doesn't work, use local language knowledge or a translation site to find the regional word and hope the first letter does it. Seems like Microsoft should produce a table for what is used with different languages.

 

[Grumm]

@Grumm But the question is - How do you find out WHAT letters need to be used in the Format() function for different languages? When you google Function() from the Netherlands, do you see different Microsoft documentation than English speakers do?

I would just check the microsoft support :

Een datum- en tijdveld opmaken - Microsoft Ondersteuning

Wanneer u het gegevenstype Datum/tijd gebruikt in Microsoft Access, kunt u de standaardnotaties aanhouden, een vooraf gedefinieerde notatie toepassen of een aangepaste notatie maken.

support.microsoft.com

Looks like Dutch uses the English letters.
(but again, it looks like nobody knows what is correct :

u of hh

Geeft het uur weer met één of twee cijfers.

in English :

h or hh

Displays the hour as one or two digits.

So what ? I can use u for 1 digit hour but not uu for 2 digits ?

French is always tricky because they don't like English and want to translate everything

 

[Grumm]

What's strange is that the french help still uses the english letters:

Fonction format​

That is because it is in VBA. VBA is (as far as I know) same functions for all.
OP was using the format expression in the designer mode.
(This is the same as Excel: you need to use language specific functions in cells. But not in VBA)

 

[sonic8]

@sonic8 did you say that the SQL View and the QBE View of a query in a non-English version would show different values? How does that work when you are creating SQL using VBA? Your dates need to be either the US format of mm/dd/yyyy or the unambiguous yyyy/mm/dd. Does that mean that you need to create the embedded SQL using the English variables but when you create it using QBE, you use the French variables?

Correct. VBA and the Access query SQL view are strictly English, regardless of the UI language selected for Access.
The query design view is completely localized to the selected UI language. This includes the names of functions and the date part identifiers for date formats.

I usually strictly use English language settings in all programs, just for demonstration I switched Office to German UI.

Here is a simple query a wrote in SQL view:

Now, I do not change anything but just switch to design view:

But the question is - How do you find out WHAT letters need to be used in the Format() function for different languages?

Well, this question is not as difficult as it may appear to you. If someone is using Office with a non-English UI, it will usually be a language they are familiar with. So, knowing the initial letters of day/month/year/hour/minute/second is not something most people would need to think about much.

In general: Please note that there are no more language specific editions of Microsoft Office for more than 10 years now. Office is a multi-language application and it is fairly easy to install an additional language to your Office installation. Once installed, you can switch to other languages in mere seconds. - Restart of the application is required though.

So, if you have to support users using Office/Access with a different language than you, just install the language in question to reproduce their experience.

 

[Pat Hartman]

Thanks. That was very helpful