Group by the first X numbers i a query? 1

[frontside]

I have a datefield formatted as text that I would like to group by the first 6 numbers.

example

20040101
20040112
20040115
20040201

the result should be two groups with the first three in the first group and the last number in the second.

I want to do this in the query not in a report,

does anyone have any good ideas or suggestions?



Thanks, and let me know if you need any more information to help me out.


micke "sweden

 

[mndrlion]

What happens if you set the query to sort ascending on this field?

 

[genomon]

In design view in query builder: Type something like SortField:Cint(Left([fieldname],6)) in the field box. Choose Ascending/Descending in the sort box. Uncheck the "show " checkbox. Hope this helps!!

 

[genomon]

OOPS - forgot about the "grouping" thing; should still work...

 

[frontside]

it worked fine with Left([fieldname],)!!!

perfect

well worth a star!!