Date format in excel embedded in access

[Dario]

Hello,
I'm trying to use a "Time-scale" axis in excel on a recordset that is
correctly handled by other forms (eg. a subform).
The problem lies in excel interpretation of date value (that are in
"dd/mm/yyyy" format): maybe that the format is passed internally in an
incorrect way.

The SQL that handles the excel rendering is the following:

SELECT (MyFormat([MovData],"mmm"" '""yy")) AS Expr1,
Sum(ReportClasseMovimentiQry.MovEntrata) AS Entrate,
-Sum(Movimenti.MovUscita) AS Uscite,
Sum(IIf(IsNull([Movimenti].[MovEntrata]),0,[Movimenti].[MovEntrata]))-Sum(IIf(IsNull([Movimenti].[MovUscita]),0,[Movimenti].[MovUscita]))
AS Saldo FROM ReportClasseMovimentiQry WHERE
(((ReportClasseMovimentiQry.MovData)>=DateValue("01/04/2002") And
(ReportClasseMovimentiQry.MovData)<=DateValue("31/07/2003"))) GROUP BY
(MyFormat([MovData],"mmm"" '""yy")),
(Year([MovData])*12+Month([MovData])-1) ORDER BY
(Year([MovData])*12+Month([MovData])-1);


Any hint? Thanks in advance

 

[Douglas J. Steele]

Try using CDate instead of DateValue. CDate definitely respects the regional
settings: I'm not sure DateValue does.