sorting by week 1

[Apollo13a]

Hello, my report has sorting and grouping by in this order: EmpLastName, Quarter, Weekly, LaborDate. I have footers after weekly and quarter to show some calculations and totals. My problem is that week 10 shows up first on the report ahead of week 6, 7, 8 and 9. I suppose Access is using the 1 in the ten to group on but can I get this in the right order?
thanks,
jim

 

[dhookom]

What are your actual expressions in the sorting and grouping dialog?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Apollo13a]

What are your actual expressions in the sorting and grouping dialog?

Do you mean the expressions that are on my report or the grouping and sorting properties?

The expresion ("WeekNum") on my actual report is an expression in my query:
WeekNum: Format([Labordate],"ww")

As for the grouping and sorting properties for the WeekNum header they are:

Group Header Yes
Group Footer No
Group On Each Value
Group Interval 1
Keep together With First Detail

So I group by EmpLastName then by Quarter then by WeekNum
and my detail is sorted by Labordate-ascending.

I hope I provided enough info and thanks for responding.
Jim

 

[dhookom]

Don't format in your query. Use DatePart("ww",[LaborDate]). If this doesn't work, wrap the expression in Val(DatePart("ww",[LaborDate])).

Formatting in queries is generally a waste and only causes issues. I try to do all of my formatting in controls on forms and reports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Apollo13a]

Yup, you gotta it, that worked perfectly.
Thanks