DatePart function problem

[ThomasJF]

Hi again

I am doing three Crosstab Querys that is supposed to show how many sales a person has weekly, monthly and Quarterly.
I used this function to fix the quarterly:

Quarter: "Qtr " & DatePart("q";[TBL_Day].[Date];1;0)

My question is: How do I alter it so it sorts by month and week? I tried:

Month: "Mth " & DatePart("m";[TBL_Day].[Date];1;0)

but that did not work. Any ideas?

By the way, the query is very simular to the "Quarterly Orders by Product" query in the "Northwind" sample database that comes with Access.


Best regards
Thomas Fyhn




[sig][/sig]

 

[Guest_imported]

Hi Thomas

When you say it 'did not work', it would be helpful to know what results/error message it came up with anyway. Makes it much easier to diagnose the problem!!

I assume the problem is that the results are sorted not by month in the order 1,2,3,4 etc but in the order 1,11,12,2,3,4 etc. If this is the case you need to apply a little formatting to prevent Access from seeing your DatePart results as a text field. You can either do:

"Mth " & Format(DatePart("m";[TBL_Day].[Date];1;0),"00&quot which makes the values to sort 01,02,03 etc

or if you could happily ditch the 'Mth' at the beginning, a purer way of doing this would be...

Val(DatePart("m";[TBL_Day].[Date];1;0)) , which forces Access to consider your results as numerics.

HTH

Nick