Group by expression-any pointers?

[Guest]

Hi,

I'm creating a report (about to go on a holiday so it must be done quick)
and need to group my data. The query has a datefield on which I would like to
group by.

In the groupheader I would like to present the datefield grouped by year.
What I need is that my data is split into records which are going to take
place this year (or earlier) and all other records which are next year and
on. So in fact i'm looking for a group by in two sets of records.

I thought I'd create an expression in the groupheader but I could use a
little help with this one.

Thanks in advance.

 

[Allen Browne]

So you want to group by all records to the end of the current year (2007) as
one group, and then future years as another group?

Type this expression into the Field row in query design:
([MyDateField] < DateSerial(Year(Date()) + 1, 1, 1))

Substitute the name of your date field for MyDateField.
Group on this field.

 

[Guest]

Thanks Allen,
I knew I would be better of using it in the query instead of the report.
cheers
--
Maurice Ausum

So you want to group by all records to the end of the current year (2007) as
one group, and then future years as another group?

Type this expression into the Field row in query design:
([MyDateField] < DateSerial(Year(Date()) + 1, 1, 1))

Substitute the name of your date field for MyDateField.
Group on this field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm creating a report (about to go on a holiday so it must be done quick)
and need to group my data. The query has a datefield on which I would like
to
group by.

In the groupheader I would like to present the datefield grouped by year.
What I need is that my data is split into records which are going to take
place this year (or earlier) and all other records which are next year and
on. So in fact i'm looking for a group by in two sets of records.

I thought I'd create an expression in the groupheader but I could use a
little help with this one.

Thanks in advance.