Table-based report, grouping by fiscal year

[Guest]

Hello all,

I'm fairly new to Access(but not office), having only had an introductory
course to Office2k3, and the last VB class I had was in 2001.

I have been tasked with creating a new report in an existing database for
contract management. This database has 3 tables, contracts, invoices, and
amendments.
Invoices and Amendments are linked to contracts by a 1 to many relationship,
contracts being 1. I'm using access 2k3 to modify the DB, but it needs to be
access 2000 compatible.

The report that I have to create needs to display contract information,
modifications, and invoice totals by month and fiscal year (July01-June30).
The fiscal year is named for the year it ends in. i.e. July 1, 2006 - June
30, 2007 is FY2007. It needs to display some more complex budget
information, but I'll save that for later, probably a different post.

The contract details a populated from an existing query that I'm able to
reuse and it works nicely. The modification are populated from a sub-report
that pulls its data from the amendments table. I'm able to reuse that nicely
as well. I'm using another sub-report to get the invoice data and I'm
pulling my data from the invoices table.

The headers and footers I have are:
Report header (empty)
Page header (empty)
invDate header (see below)
Detail (see below)
invDate footer (see below)
invDate footer (see below)
Page footer (empty)
Report footer (see below)

The problem I'm having is that I'm unable to get the data to sort the way I
want. I'm a little unsure how to describe the layout so please bear with me.
I'm using a outline type view going from year to month with a subtotal for
the year and a grand total at the end of the report. The date field is
invDate. There are 2 sort&order fields, both are set to invDate, the first
by year and the second by month. In the invDate header I have the text box
that displays the year value and some labels, I've been using
=Format$([invDate],"yyyy") In the detail section I have a textbox set to
invDate, no formatting, and another textbox that calculates the total invoice
amount for each entry. The detail section is hidden.

In the first invDate footer I have 2 textboxes. The first to display the
month using =Format$([invDate],"mmmm") This has also prevented the
individual invoices from being displayed. The second textbox calculates the
total for each month, I've been using a textbox with
=Sum([invRountineAmt]+[invExtraAmt])
In the second invDate footer I only have a label and another textbox to
calculate the total for the year, using =Sum([invRountineAmt]+[invExtraAmt])
Lastly in the Report footer, I have a label and a textbox that calculates
the total for the entire contract, again using
=Sum([invRountineAmt]+[invExtraAmt])

The problem that I'm having is that I've tried using DateAdd in sort&order,
but either I'm doing something wrong, not enough, or it won't work. In the
textboxes, I've tried using =Format(DateAdd("yyyy",1,[invDate]),"yyyy") to
get the proper year, which seems to work. I've never messed with the date
text box in the details section. And in the date textbox in the footer, I've
tried =Format(DateAdd("m",6,[invDate]),"mmmm") which advances the month by
six. Trouble is, is that it's just changing the name, not truely advanceing
to six months ahead for the year. I need the total fields to display the
proper numbers for their month.

For example, before any formatting the only invoice for Jan. 2002 with a
total of $684 would show Jan. and display in Year 2002. After the month
changes the month would show June but the number would still be $684, the
number from Jan. The total textbox does not calculate the june values.

I'm not sure where I should proceed from here. If guys need any more
information just ask and I'll see what I can do. Any help you guys can give
is greatly appreciated.

 

[Allen Browne]

Create a query.
In the Field row in query design, in a fresh column, enter:
FinYear: DateAdd("m", -6, [invDate])

This gives you a calculated field named FinYear, which is the same for all
months in a financial year. Now in your report, you can use FinYear on the
first row of the Sorting'n'Grouping dialog, with its own Group Header and
Group Footer. To show the year, add a text box with Control Source of:
=[FinYear] & " / " & ([FinYear] + 1)

On the 2nd row of the Sorting'n'Grouping box, you can then choose invDate,
and in the lower pane group by month (or quarter or whatever), with a Group
Header and Group Footer again for the monthly total. To show the month in
the group header, add a text box bound to the invDate field, and set its
Format property to:
mmmm yyyy

On the 3rd row, choose invDate again, so the records sort in the right
order.

Hopefully you can adapt that to exactly what you need.

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

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

Hello all,

I'm fairly new to Access(but not office), having only had an introductory
course to Office2k3, and the last VB class I had was in 2001.

I have been tasked with creating a new report in an existing database for
contract management. This database has 3 tables, contracts, invoices, and
amendments.
Invoices and Amendments are linked to contracts by a 1 to many
relationship,
contracts being 1. I'm using access 2k3 to modify the DB, but it needs to
be
access 2000 compatible.

The report that I have to create needs to display contract information,
modifications, and invoice totals by month and fiscal year
(July01-June30).
The fiscal year is named for the year it ends in. i.e. July 1, 2006 -
June
30, 2007 is FY2007. It needs to display some more complex budget
information, but I'll save that for later, probably a different post.

The contract details a populated from an existing query that I'm able to
reuse and it works nicely. The modification are populated from a
sub-report
that pulls its data from the amendments table. I'm able to reuse that
nicely
as well. I'm using another sub-report to get the invoice data and I'm
pulling my data from the invoices table.

The headers and footers I have are:
Report header (empty)
Page header (empty)
invDate header (see below)
Detail (see below)
invDate footer (see below)
invDate footer (see below)
Page footer (empty)
Report footer (see below)

The problem I'm having is that I'm unable to get the data to sort the way
I
want. I'm a little unsure how to describe the layout so please bear with
me.
I'm using a outline type view going from year to month with a subtotal for
the year and a grand total at the end of the report. The date field is
invDate. There are 2 sort&order fields, both are set to invDate, the
first
by year and the second by month. In the invDate header I have the text
box
that displays the year value and some labels, I've been using
=Format$([invDate],"yyyy") In the detail section I have a textbox set to
invDate, no formatting, and another textbox that calculates the total
invoice
amount for each entry. The detail section is hidden.

In the first invDate footer I have 2 textboxes. The first to display the
month using =Format$([invDate],"mmmm") This has also prevented the
individual invoices from being displayed. The second textbox calculates
the
total for each month, I've been using a textbox with
=Sum([invRountineAmt]+[invExtraAmt])
In the second invDate footer I only have a label and another textbox to
calculate the total for the year, using
=Sum([invRountineAmt]+[invExtraAmt])
Lastly in the Report footer, I have a label and a textbox that calculates
the total for the entire contract, again using
=Sum([invRountineAmt]+[invExtraAmt])

The problem that I'm having is that I've tried using DateAdd in
sort&order,
but either I'm doing something wrong, not enough, or it won't work. In
the
textboxes, I've tried using =Format(DateAdd("yyyy",1,[invDate]),"yyyy")
to
get the proper year, which seems to work. I've never messed with the date
text box in the details section. And in the date textbox in the footer,
I've
tried =Format(DateAdd("m",6,[invDate]),"mmmm") which advances the month
by
six. Trouble is, is that it's just changing the name, not truely
advanceing
to six months ahead for the year. I need the total fields to display the
proper numbers for their month.

For example, before any formatting the only invoice for Jan. 2002 with a
total of $684 would show Jan. and display in Year 2002. After the month
changes the month would show June but the number would still be $684, the
number from Jan. The total textbox does not calculate the june values.

I'm not sure where I should proceed from here. If guys need any more
information just ask and I'll see what I can do. Any help you guys can
give
is greatly appreciated.

 

[Guest]

It took some tweeking and rearranging, but I got things squared away. I
ended up using =DatePart("yyyy",[FinYear]) & " / " &
(DatePart("yyyy",[FinYear])+1) to get the fiscal year to display properly.
Just by itself, the expression you posted was only adding 1 day instead of a
year. Oh well, moot point now. Now on to the budget stuff, I'll make new
post if I need help there.

Thanks for your help.