Date display format

[Ali29J]

Hi all
still havin problems...

I am using the following SQL

SELECT DISTINCTROW Format$(SalesPersonPotentialBG.[Quotation Date],'yyyy mmmm') AS [Date By Month], (Sum(Nz(SalesPersonPotentialBG.calculatedtotal1,0))+Sum(Nz(SalesPersonPotentialBG.calculatedtotal2,0))+Sum(Nz(SalesPersonPotentialBG.calculatedtotal,0))) AS [Sum USD]
FROM SalesPersonPotentialBG
GROUP BY Format$(SalesPersonPotentialBG.[Quotation Date],'yyyy mmmm'), Year(SalesPersonPotentialBG.[Quotation Date])*12+DatePart('m',SalesPersonPotentialBG.[Quotation Date])-1
ORDER BY Format$(SalesPersonPotentialBG.[Quotation Date],'yyyy mmmm');

which orders correctly with year but alphabetically on month, so i tried to change order by to yyyymmdd, in order to order correctly but i get the following message, am i missing some code...
"
tried to execute query that does not include specified expression 'Formats$(SalesPersonPotentialBG.[Quotation D
Date],'yyyymmdd')' as aggregate function.
"

Thanks

Ali

 

[PHV]

You may try this:
GROUP BY Format$(SalesPersonPotentialBG.[Quotation Date],'yyyy mmmm'), Format$(SalesPersonPotentialBG.[Quotation Date],'yyyymm')
ORDER BY Format$(SalesPersonPotentialBG.[Quotation Date],'yyyymm');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886