Grouping data & date values?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to retrieve records that run from 10/1 - 9/30 for the policy years.
With using Between, I can specify each policy year ex: Between 10/01/2003
AND 10/1/2004, but I don't want to do that for every year - the db goes back
to 1987 through current. I need it grouped by policy year. All policy years
would be in the same report.
 
To treat Oct-Dec as part of the next year, group by this expression:
Year(DateAdd("m", 3, [MyDateField]))
 
Hi Allen,

I still need help. Ex: the policy year runs from 10/01/2004 -
9/30/2004...and the same for all years dating back to 1987. I used the
formula you wrote in the grouping in the report - but I get an error msg of
too many ")" but the syntax is correct. Am I using it in the wrong place?

Allen Browne said:
To treat Oct-Dec as part of the next year, group by this expression:
Year(DateAdd("m", 3, [MyDateField]))

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

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

Shae said:
I need to retrieve records that run from 10/1 - 9/30 for the policy years.
With using Between, I can specify each policy year ex: Between 10/01/2003
AND 10/1/2004, but I don't want to do that for every year - the db goes
back
to 1987 through current. I need it grouped by policy year. All policy
years
would be in the same report.
Click to expand...
Click to expand...
 
When using an expression, preface it with "="
=Year(DateAdd("m", 3, [MyDateField]))
This is a "control source" much like the control source of a text box which
would also require the "=".

--
Duane Hookom
MS Access MVP
--

Shae said:
Hi Allen,

I still need help. Ex: the policy year runs from 10/01/2004 -
9/30/2004...and the same for all years dating back to 1987. I used the
formula you wrote in the grouping in the report - but I get an error msg
of
too many ")" but the syntax is correct. Am I using it in the wrong place?

Allen Browne said:
To treat Oct-Dec as part of the next year, group by this expression:
Year(DateAdd("m", 3, [MyDateField]))

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

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

Shae said:
I need to retrieve records that run from 10/1 - 9/30 for the policy
years.
With using Between, I can specify each policy year ex: Between
10/01/2003
AND 10/1/2004, but I don't want to do that for every year - the db goes
back
to 1987 through current. I need it grouped by policy year. All policy
years
would be in the same report.
Click to expand...
Click to expand...
Click to expand...
 
Try creating a query to use as the RecordSource for your report.

Type the expression into the Field row of the query, in a fresh column.

This gives you an extra field in your query, and you can then use this field
in sorting'n'grouping in your report.

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

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

Shae said:
I still need help. Ex: the policy year runs from 10/01/2004 -
9/30/2004...and the same for all years dating back to 1987. I used the
formula you wrote in the grouping in the report - but I get an error msg
of
too many ")" but the syntax is correct. Am I using it in the wrong place?

Allen Browne said:
To treat Oct-Dec as part of the next year, group by this expression:
Year(DateAdd("m", 3, [MyDateField]))

Shae said:
I need to retrieve records that run from 10/1 - 9/30 for the policy
years.
With using Between, I can specify each policy year ex: Between
10/01/2003
AND 10/1/2004, but I don't want to do that for every year - the db goes
back
to 1987 through current. I need it grouped by policy year. All policy
years
would be in the same report.
Click to expand...
Click to expand...
Click to expand...
 
I finally did an expression - in a query - that gave the results I was
looking for. THANK YOU! so much for your help.

Allen Browne said:
Try creating a query to use as the RecordSource for your report.

Type the expression into the Field row of the query, in a fresh column.

This gives you an extra field in your query, and you can then use this field
in sorting'n'grouping in your report.

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

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

Shae said:
I still need help. Ex: the policy year runs from 10/01/2004 -
9/30/2004...and the same for all years dating back to 1987. I used the
formula you wrote in the grouping in the report - but I get an error msg
of
too many ")" but the syntax is correct. Am I using it in the wrong place?

Allen Browne said:
To treat Oct-Dec as part of the next year, group by this expression:
Year(DateAdd("m", 3, [MyDateField]))

I need to retrieve records that run from 10/1 - 9/30 for the policy
years.
With using Between, I can specify each policy year ex: Between
10/01/2003
AND 10/1/2004, but I don't want to do that for every year - the db goes
back
to 1987 through current. I need it grouped by policy year. All policy
years
would be in the same report.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top