Change MONTH date language within a query

[ian_ok]

I'm trying to change the month language when I produce a query so that when the data from the query is shown in a report the language of the date is shown correctly.

A report is shown in English and the date is 1 January 2011

When the report is for a Spanish client I want 1 enero 2011

I have the following code but it says I have a syntax error, plus not sure if I am barking up the wrong tree and should do this a different way anyhow.

test: Select Day ([Arrival Date]) & " " & Choose(Month([Arrival Date]), "Enero", "Febero", "Marzo") & " " & Year([Arrival Date]) As FormattedDate;

Ian

 

[jdraw]

Your error is with the Select part of your string.

This works

Sub spTest()

' "Enero", "Febero", "Marzo"

Dim MyDate As Date
MyDate = Date
Debug.Print Choose(Month(MyDate), "Enero", "Febero", "Marzo")
End Sub

 

[namliam]

If your spanish client has the "region" settings set to spanish a Format of DD MMMM YYYY will automaticaly be spanish (or what ever region settings your pc is set to).

The reason your test thing is failing, assuming it is the full SQL, there is no "from" in it....
Other than that it looks OK at first glance.

 

[ian_ok]

To the first reply, where would I put that in a query...? The reason i need it in a query the dates are used later in the report in the dd/mm/yyyy format which I don't need to amend, just in pone section of the report needs changing.

To the 2nd reply I'm a tad lost as it's been 15 years since I did my last d/b...! I can't just change the Reg Settings as it's all done on my 2 pc's and the report produces an invoice which is in Eng or Esp and the esp date needs to show enero 7 not january and i don't want staff messing with settings.

I tried this to no avail:
test: SELECT ([Arrival Date]) FROM Reservations, & " " & Choose(Month([Arrival Date]), "Enero", "Febero", "Marzo") & " " & Year([Arrival Date]) As FormattedDate;

 

[DCrake]

When you say it does not work what is wrong with it? It worked for me.

 

[ian_ok]

It says check the subkeys and ensure it's got the brackets

 

[DCrake]

this is my query syntax

SELECT TblDockets.D_Date, Choose(Month([D_Date]),"Enero","Febero","Marzo") & " " & Year([D_Date]) AS Prd
FROM TblDockets;

 

[ian_ok]

Oh well i can't get it to work on the current d/b but when I recreate your code on it own in a new d/b it works fine......probably something simple, will report back soon.

Ian

 

[ian_ok]

Works wonders, thanks a million from a Bolton supporter...!!!

 

[DCrake]

Swearing and offensive language is not permitted on this forum

 

[ian_ok]

Yep I had to leave that until the end or else you might have done a Coyle and left before you had finished the job....!!

Thanks again.

Cheers Ian

 

[DCrake]

very funny LOL