YTD figures on a report

[Tony Williams]

I have a table "tblmaintabs" that stores data that is collected from various
companies on a quarterly basis in March, June, September and December each
year (these dates are stored in a Date/time field called txtmonthlabel
formatted mmmm yyyy). I am trying to create a report that shows the figures
for the same quarter last year and the figures for the year to date this
year and last year. The value is shown on the report in the control
"txtDomfactot" based on a field with the same name in my table. I have
managed to work out how to show the same quarter's figures last year by
using DLookup, but I want to be able to show on my report the year to date
figures but cannot find the right formula to do this. The report needs to
look like this say for June 2004:

Qtrly Figures
YTD Figures
(June 2004) (June 2003)
(June2004) (June 2003)
txtDomfactot 9999 9999 888888
888888

I have built an input form "frmdate" where the user can input the current
qtr say June 2004 ( the control on the form is txtqtr2 and is also a
Date/Time format mmmm yyyy) There is a command button that opens the report
with the data showing the quarterly figures. The figure for the same qtr
last year I get from calculating the quarter number
=DateAdd("q",-4,[txtqtr2]) and using DLookup to find the value of
txtdomfactot. So far OK!

The problem is how do I calculate the year to date figure bearing in my that
in March it will be the same as the qtrly figure, in June it will be March
+June, in September it will be March + June + September and in December it
will be the total of all the individual quarters' figures.

I've tried to use DLookup in various ways eg calculating the quarter number
and then using DLookup again to get the value of txtdomfact for that quarter
using a series of IIf statements to determine which quarter I'm in, and
other derivations of this but all I get is #Error where the value should be.
Am I using the right method and can anyone give me a clue as to where I go
from here?
TIA
Tony Williams

[misscrf]

I would suggest creating a build in the underlying query for the
report. The build would look like this - ---Year:
Right([datefield],4) --- Then for the criteria put a parameter like so:
Forms!formtopickcriterianame!comboortextname

On the form where the user picks the ytd that they want, you can do one
of two things. You can have a text field for the user to type in the
year, (2004) or you can use a combo that will lookup only the last 4
digits of the date field, being the year, and group by it. This will
give you the years that exist in your table.

Then the report just finds records or totals that exist only within
that criteria ( date having those last 4 digits) - that gives you the
year.

[misscrf]

try this in the query : Right([DATE],4) then put criteria to a field
on a form for the user to choose date. Should restrict to year.

[Tony Williams]

Thanks could you explain what you mean by "create a build"?
Thanks for your help
Tony
"misscrf" <mi*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I would suggest creating a build in the underlying query for the
report. The build would look like this - ---Year:
Right([datefield],4) --- Then for the criteria put a parameter like so:
Forms!formtopickcriterianame!comboortextname

On the form where the user picks the ytd that they want, you can do one
of two things. You can have a text field for the user to type in the
year, (2004) or you can use a combo that will lookup only the last 4
digits of the date field, being the year, and group by it. This will
give you the years that exist in your table.

Then the report just finds records or totals that exist only within
that criteria ( date having those last 4 digits) - that gives you the
year.

[misscrf]

Sure, Tony. To create a build on a field, do the following.

1) go to the design view of your report

2) bring up the form properties, by right clicking on the left top
corner of the report.

3) go to it's control source, and click the ... to invoke the query
builder

4) bring in every field you need for your report - from here I tend to
preview this query as I work to make sure I am getting the data I want
to see, if I need grouping, as a create formulas, I keep checking it to
ensure that what I am doing will show up on the report correctly.

5) once you have all of the fields you need in the query design, on an
empty field space, right click where the field name goes, and choose
the builder. Then make the formula. You will have to play around with
syntax. Don't be afraid of the help.

6) read this whole step before doing it! Don't try to save the query
by clicking save - INSTEAD click the inner x to close the query. When
it asks if you want to save the changes you have made to the underlying
query click YES. Then it becomes the control source of your report.

Many times I have tried to do formulas in fields on the report, and
they tend to work better in the query builder and then placed as a
field on the report.

You can also place your criteria in the query, which would be
references to form fields. I rarely have a report that doesn't use
that.

[misscrf]

Sure, Tony. To create a build on a field, do the following.

1) go to the design view of your report

2) bring up the form properties, by right clicking on the left top
corner of the report.

3) go to it's control source, and click the ... to invoke the query
builder

4) bring in every field you need for your report - from here I tend to
preview this query as I work to make sure I am getting the data I want
to see, if I need grouping, as a create formulas, I keep checking it to
ensure that what I am doing will show up on the report correctly.

5) once you have all of the fields you need in the query design, on an
empty field space, right click where the field name goes, and choose
the builder. Then make the formula. You will have to play around with
syntax. Don't be afraid of the help.

6) read this whole step before doing it! Don't try to save the query
by clicking save - INSTEAD click the inner x to close the query. When
it asks if you want to save the changes you have made to the underlying
query click YES. Then it becomes the control source of your report.

Many times I have tried to do formulas in fields on the report, and
they tend to work better in the query builder and then placed as a
field on the report.

You can also place your criteria in the query, which would be
references to form fields. I rarely have a report that doesn't use
that.