impossible to get year in format date

[ryprobg]

hello:
i use this formula in an access query to get year/mounth and [deb] is my date field in the table
expr1: Format([deb];"""yyyy""/mm") but when i execute the query i get the result shown in the picture below
i use frensh windows and the language of my computer is frensh / format of deb field ( Date, abrégé)
how can i get year/mounth with a correct formula?

 

[cheekybuddha]

expr1: Format([deb]; "yyyy/mm")

 

[ryprobg]

expr1: Format([deb]; "yyyy/mm")

yes this is what i use expr1: Format([deb]; "yyyy/mm") but give just the result in the picture below

 

[cheekybuddha]

That's not what you wrote in your original post.

You wrote: expr1: Format([deb];"""yyyy""/mm")

I wrote: expr1: Format([deb]; "yyyy/mm")

It's different.

 

[ryprobg]

That's not what you wrote in your original post.

You wrote: expr1: Format([deb];"""yyyy""/mm")

I wrote: expr1: Format([deb]; "yyyy/mm")

It's different.

yes is the same when i entre expr1: Format([deb]; "yyyy/mm") the system gives expr1: Format([deb];"""yyyy""/mm") on the query createria

 

[cheekybuddha]

OK, perhaps try:
expr1: Format([deb]; 'yyyy/mm')

 

[ryprobg]

OK, perhaps try:
expr1: Format([deb]; 'yyyy/mm')

the same problem result yyyy/09

 

[LarryE]

Instead of using a ; (semi-colon) as a separator in the Format Function, try using a , (comma) so:
Format([deb],"yyyy/mm")
or try:
Year([deb] & "/" & Month([deb])

 

[Gasman]

You and Dates do not get on do you?

SELECT TestTransactions.TransactionDate, Format([TransactionDate],"yyyy/mm") AS Expr1
FROM TestTransactions;

So what do you have in Deb? Is it even a Date (in numerical form?)

 

[Gasman]

Instead of using a ; (semi-colon) as a separator in the Format Function, try using a , (comma) so:
Format([deb],"yyyy/mm")
or try:
Year([deb] & "/" & Month([deb])

I tried that, but it removes the leading zero for Month?

 

[ryprobg]

Instead of using a ; (semi-colon) as a separator in the Format Function, try using a , (comma) so:
Format([deb],"yyyy/mm")
or try:
Year([deb] & "/" & Month([deb])

no in frensh windows ; replace ,

 

[cheekybuddha]

Try:
expr1: Format(CDate([deb]); 'yyyy/mm')

 

[Gasman]

expr1: Format(CDate([deb]); 'yyyy/mm') even?
I mention it as OP would copy that as is

 

[cheekybuddha]

Answering on my phone! Blinking auto-corrupt!

Already fixed

Cheers

 

[ryprobg]

resolved!

 

[cheekybuddha]

année vs year !!!

Hahahaha!

Will have to remember that!

 

[ryprobg]

année vs year !!!

Hahahaha!

Will have to remember that!

hahahahahaha yes glued by iron in my brain hahahaha

 

[cheekybuddha]

Do you have to use jj instead of dd as well,

eg Format([deb],"aaaa/mm/jj")

?

 

[sonic8]

@ryprobg, when asking questions about queries here at AWF, it is probably sensible to use the SQL of your queries as a reference. SQL View in Access will display the original English language representation of the query instead of the localized French translation from the Design View.

Additional Bonus: The SQL of the query can be pasted here as text and is a more precise and concise description of the query than a screenshot of the Design View.

 

[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