data type mismatch

[ali32j]

Hi all

I am having a problem with a query which up until now has been working fine....

i ve identified the problem with the following code:

ReportOrder: Cdate(Month([SalesReport].[Month]) & "/01/"&Year([SalesReport].[Month]))

It basically say data type mismatch...

I understand that there is some issue with the datatype of Month, but i dont understand what the issue is...

Can anyone help with where to start?

Ali

 

[PHV]

What is the data type of Month in SalesReport ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ali32j]

Hi PHV

Date/time - format: Short date

Ali

 

[PHV]

So, you may try this:
ReportOrder: Format(SalesReport.Month, 'yyyymm')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ali32j]

Hi PHV

That works fine but issue i have is when i put this query into a pivot chart it does not order correctly, i use display month along x-axis and this orders alphabetically

Query is as follows:

SELECT DISTINCTROW Sum(SalesReport.SalesValue) AS [Sum Of SalesValue], Sum(SalesReport.TargetValue) AS [Sum Of TargetValue], Sum(SalesReport.TargetPerc) AS [Sum Of TargetPerc], Sum(SalesReport.GrowthPerc) AS [Sum Of GrowthPerc], [SalesValue]*(100/(100+(100*[GrowthPerc]))) AS [SalesValue-1], CStr(Format$(SalesReport.Month,'mmmm yyyy')) AS DisplayMonth, Format([SalesReport].[Month],'yyyymm') AS ReportOrder
FROM SalesReport
WHERE (((SalesReport.Month)>=DateSerial(Year(Date())-1,Month(Date())+1,0)))
GROUP BY [SalesValue]*(100/(100+(100*[GrowthPerc]))), CStr(Format$(SalesReport.Month,'mmmm yyyy')), Year(SalesReport.Month)*12+DatePart('m',SalesReport.Month)-1, Format([SalesReport].[Month],'yyyymm')
ORDER BY Format([SalesReport].[Month],'yyyymm');

How can i order correctly but also show month/year along x-axis of my pivot chart?

Ali

 

[PHV]

You may replace the 2 ooccurences of this:
CStr(Format$(SalesReport.Month,'mmmm yyyy'))
to this:
Format(SalesReport.Month,'mm_mmmm yyyy')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886