T
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
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