Format date Month/Year

[Guest]

I have a date field that is formatted 05/12/2004. I need to use the date in
a crosstab query that will group by month/year like 05/2004. I'm currently
using format([HireDate],"mm/yy"). The crosstab query looks correct but I'm
using that crosstab query in a graph and the dates are showing as 05/2005,
etc. My guess is that there is a better way to format my date in my
underlying query. Any ideas? Thank you.

 

[Guest]

Post the SQL.

 

[Guest]

Thanks Karl -

My first query SQL is:

SELECT NewHireQuery.*, Format([HireDate],"mm/yy") AS Hire_Date
FROM NewHireQuery;

My crosstab query, which uses the above query is:

TRANSFORM Count(ChartCodeHireDateOneNH.RegTemp) AS CountOfRegTemp
SELECT ChartCodeHireDateOneNH.County1 AS County
FROM ChartCodeHireDateOneNH
WHERE (((ChartCodeHireDateOneNH.GenderDesc) Like '*') AND
((ChartCodeHireDateOneNH.EthnicDescription) Like '*') AND
((ChartCodeHireDateOneNH.EducationDescription) Like '*') AND
((ChartCodeHireDateOneNH.Code) In ('New')) AND
((ChartCodeHireDateOneNH.JobTitle) Like '*') AND
((ChartCodeHireDateOneNH.LengthCategory) Like '*') AND
((ChartCodeHireDateOneNH.GroupDescription) Like '*') AND
((ChartCodeHireDateOneNH.County1) Like '*') AND
((ChartCodeHireDateOneNH.HireDate) Between #1/1/2005# And #10/1/2005#))
GROUP BY ChartCodeHireDateOneNH.County1
PIVOT ChartCodeHireDateOneNH.Hire_Date;

Post the SQL.

I have a date field that is formatted 05/12/2004. I need to use the date in
a crosstab query that will group by month/year like 05/2004. I'm currently
using format([HireDate],"mm/yy"). The crosstab query looks correct but I'm
using that crosstab query in a graph and the dates are showing as 05/2005,
etc. My guess is that there is a better way to format my date in my
underlying query. Any ideas? Thank you.