Summing a calculated control in a report

[Tony Williams]

I have a calculated control in the detail section of a report. The
calculation is
=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthla
bel]= [txtqtr1]"))
The control is called prevqtramount. In a footer I want to calculate the sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive tried
repeating the calculation and used
=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMon
thlabel]= [txtqtr1]"))) but that doesn't work either.

What am I doing wrong?
Thanks
Tony

 

[Duane Hookom]

Since the DLookup() returns the same exact value for every record in your
report, I would
- add tblMainTabs to the report's record source
- add txtDomFacsoleqtr to the grid and alias it DomQtr
- add txtMonthlabel to the grid and set its criteria to txtqtr1
In your report, you and then use DomQtr rather than the DLookup().

 

[Tony Williams]

Thanks Duane but I wonder whether you could elaborate for me. I perhaps
should have given you more information as well.

Firstly my report is based on a query and tblMaintabs is the table that the
query is based on. How do I have more than one record source for my report?

What do you mean when you say "add txtDomFacsoleqtr to the grid and alias it
DomQtr" The control txtDomFacsoleqtr is in the detail section of the
report and

I'm not quite sure what you mean by "alias" Is that use Domqtr as the name
of the control as distinct from txtDomFacsoleqtr?

The control txtMonthlabel is also in the detail, how do I set its
"Criteria" in the properties?

The control which contains the expression is called prevqtramount and when I
run the report I get an input box asking me to complete this value. It's as
if the calculation =Sum([prevqtramount]) in the footer can't find the
control in the detail.

I apologise for my ignorance here but really appreciate your input and help
Thanks again
Tony

Since the DLookup() returns the same exact value for every record in your
report, I would
- add tblMainTabs to the report's record source
- add txtDomFacsoleqtr to the grid and alias it DomQtr
- add txtMonthlabel to the grid and set its criteria to txtqtr1
In your report, you and then use DomQtr rather than the DLookup().

--
Duane Hookom
MS Access MVP
--

I have a calculated control in the detail section of a report. The
calculation is
=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthla
bel]= [txtqtr1]"))
The control is called prevqtramount. In a footer I want to calculate the
sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive tried
repeating the calculation and used
=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMon
thlabel]= [txtqtr1]"))) but that doesn't work either.

What am I doing wrong?
Thanks
Tony

 

[Duane Hookom]

First, I was confused by your use of:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")
I had believed the expression would have to be:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","'" & [txtMonthlabel] & "'=
[txtqtr1]")
Apparently Access resolves your expression ok.

I get very confused when I see a prefix like "txt" on a field name. Usually
a prefix like this is limited only to text box controls on forms.

You can't sum a control. Is txtDomfacsoleqtr a field in your report's record
source?

Maybe you should provide some information about your report, table(s), and
what you expect to create.


--
Duane Hookom
MS Access MVP
--

Thanks Duane but I wonder whether you could elaborate for me. I perhaps
should have given you more information as well.

Firstly my report is based on a query and tblMaintabs is the table that
the
query is based on. How do I have more than one record source for my
report?

What do you mean when you say "add txtDomFacsoleqtr to the grid and alias
it
DomQtr" The control txtDomFacsoleqtr is in the detail section of the
report and

I'm not quite sure what you mean by "alias" Is that use Domqtr as the name
of the control as distinct from txtDomFacsoleqtr?

The control txtMonthlabel is also in the detail, how do I set its
"Criteria" in the properties?

The control which contains the expression is called prevqtramount and when
I
run the report I get an input box asking me to complete this value. It's
as
if the calculation =Sum([prevqtramount]) in the footer can't find the
control in the detail.

I apologise for my ignorance here but really appreciate your input and
help
Thanks again
Tony

Since the DLookup() returns the same exact value for every record in your
report, I would
- add tblMainTabs to the report's record source
- add txtDomFacsoleqtr to the grid and alias it DomQtr
- add txtMonthlabel to the grid and set its criteria to txtqtr1
In your report, you and then use DomQtr rather than the DLookup().

--
Duane Hookom
MS Access MVP
--

I have a calculated control in the detail section of a report. The
calculation is
=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthla
bel]= [txtqtr1]"))
The control is called prevqtramount. In a footer I want to calculate
the
sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive tried
repeating the calculation and used
=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMon
thlabel]= [txtqtr1]"))) but that doesn't work either.

What am I doing wrong?
Thanks
Tony

 

[Tony Williams]

Thanks Duane, sorry for the confusion, I'm learning as I go along here!
Yes, txtDomfacsoleqtr is a field in my table and holds year to date data for
a number of companies which they supply on a quarterly basis. The table is
called tblmaintabs.
The txtmonthlabel is also a field in my table and is the date of the current
quarter, eg March 2004
The txtqtr1 is a calculated control which calculates the previous quarter
date using =DateAdd("m",-3,[txtmonthlabel])
The report I'm trying to produce does two things.
Firstly I want to compare the data for the current YTD with the previous
quarter's YTD. I was using the DLookup expression to find the data for the
previous quarter. I then wanted to calculate the difference between the two
to arrive at the quarter's data in other words Current YTD-PrevQuarter's
YTD=Quarterly change. I was trying to use the current YTD data minus my
DLookup expression to get this.
Having got these figures in the detail section I then wanted to total both
lots of data to arrive at the total figures for all the companies.

I hope that makes it a little clearer

Am I going about this in the right way or am I way off beam?

Thanks for the input, your help is always appreciated and I've already
learned a lot from you on previous postings of mine (sorry that sounds like
creeping it's not meant to, I genuinely value your input and all the experts
here), working in isolation like I do it's difficult not having other
colleagues to bounce ideas off.

Thanks again
Tony

First, I was confused by your use of:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")
I had believed the expression would have to be:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","'" & [txtMonthlabel] & "'=
[txtqtr1]")
Apparently Access resolves your expression ok.

I get very confused when I see a prefix like "txt" on a field name. Usually
a prefix like this is limited only to text box controls on forms.

You can't sum a control. Is txtDomfacsoleqtr a field in your report's record
source?

Maybe you should provide some information about your report, table(s), and
what you expect to create.


--
Duane Hookom
MS Access MVP
--

Thanks Duane but I wonder whether you could elaborate for me. I perhaps
should have given you more information as well.

Firstly my report is based on a query and tblMaintabs is the table that
the
query is based on. How do I have more than one record source for my
report?

What do you mean when you say "add txtDomFacsoleqtr to the grid and alias
it
DomQtr" The control txtDomFacsoleqtr is in the detail section of the
report and

I'm not quite sure what you mean by "alias" Is that use Domqtr as the name
of the control as distinct from txtDomFacsoleqtr?

The control txtMonthlabel is also in the detail, how do I set its
"Criteria" in the properties?

The control which contains the expression is called prevqtramount and when
I
run the report I get an input box asking me to complete this value. It's
as
if the calculation =Sum([prevqtramount]) in the footer can't find the
control in the detail.

I apologise for my ignorance here but really appreciate your input and
help
Thanks again
Tony

Since the DLookup() returns the same exact value for every record in your
report, I would
- add tblMainTabs to the report's record source
- add txtDomFacsoleqtr to the grid and alias it DomQtr
- add txtMonthlabel to the grid and set its criteria to txtqtr1
In your report, you and then use DomQtr rather than the DLookup().

=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthla

bel]= [txtqtr1]"))
The control is called prevqtramount. In a footer I want to calculate
the
sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive tried
repeating the calculation and used

=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMon

thlabel]= [txtqtr1]"))) but that doesn't work either.

What am I doing wrong?
Thanks
Tony

 

[Tony Williams]

Duane I've just noticed that my DLookup expression only uses one value for
all companies! It looks like it uses the first calculation for the first
company and doesn't change when the company changes???
I'm confused
Tony

First, I was confused by your use of:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")
I had believed the expression would have to be:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","'" & [txtMonthlabel] & "'=
[txtqtr1]")
Apparently Access resolves your expression ok.

I get very confused when I see a prefix like "txt" on a field name. Usually
a prefix like this is limited only to text box controls on forms.

You can't sum a control. Is txtDomfacsoleqtr a field in your report's record
source?

Maybe you should provide some information about your report, table(s), and
what you expect to create.


--
Duane Hookom
MS Access MVP
--

Thanks Duane but I wonder whether you could elaborate for me. I perhaps
should have given you more information as well.

Firstly my report is based on a query and tblMaintabs is the table that
the
query is based on. How do I have more than one record source for my
report?

What do you mean when you say "add txtDomFacsoleqtr to the grid and alias
it
DomQtr" The control txtDomFacsoleqtr is in the detail section of the
report and

I'm not quite sure what you mean by "alias" Is that use Domqtr as the name
of the control as distinct from txtDomFacsoleqtr?

The control txtMonthlabel is also in the detail, how do I set its
"Criteria" in the properties?

The control which contains the expression is called prevqtramount and when
I
run the report I get an input box asking me to complete this value. It's
as
if the calculation =Sum([prevqtramount]) in the footer can't find the
control in the detail.

I apologise for my ignorance here but really appreciate your input and
help
Thanks again
Tony

Since the DLookup() returns the same exact value for every record in your
report, I would
- add tblMainTabs to the report's record source
- add txtDomFacsoleqtr to the grid and alias it DomQtr
- add txtMonthlabel to the grid and set its criteria to txtqtr1
In your report, you and then use DomQtr rather than the DLookup().

=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthla

bel]= [txtqtr1]"))
The control is called prevqtramount. In a footer I want to calculate
the
sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive tried
repeating the calculation and used

=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMon

thlabel]= [txtqtr1]"))) but that doesn't work either.

What am I doing wrong?
Thanks
Tony

 

[Duane Hookom]

From a reply to you in www.tek-tips.com
I would create a Crosstab query that has the amounts for the current quarter
and the previous 4 quarters.
TRANSFORM Sum(Amount) AS SumOfAmount
SELECT Company
FROM tblTW
GROUP BY Company
PIVOT "Q" & DateDiff("q",[Return Date],Date()) In
("Q0","Q1","Q2","Q3","Q4");

Company Q0 Q1 Q2 Q3 Q4
CompA 231000 175000
CompB 150000 75000
CompC 250000 150000
CompD 175000 75000

This allows you to create a report based on the same fields/columns
everytime. You can subtract on quarter from another in the report as well as
create your totals.

--
Duane Hookom
MS Access MVP
--

Duane I've just noticed that my DLookup expression only uses one value for
all companies! It looks like it uses the first calculation for the first
company and doesn't change when the company changes???
I'm confused
Tony

First, I was confused by your use of:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")
I had believed the expression would have to be:
DLookUp("[txtDomfacsoleqtr]","tblmaintabs","'" & [txtMonthlabel] & "'=
[txtqtr1]")
Apparently Access resolves your expression ok.

I get very confused when I see a prefix like "txt" on a field name. Usually
a prefix like this is limited only to text box controls on forms.

You can't sum a control. Is txtDomfacsoleqtr a field in your report's record
source?

Maybe you should provide some information about your report, table(s),
and
what you expect to create.


--
Duane Hookom
MS Access MVP
--

Thanks Duane but I wonder whether you could elaborate for me. I perhaps
should have given you more information as well.

Firstly my report is based on a query and tblMaintabs is the table that
the
query is based on. How do I have more than one record source for my
report?

What do you mean when you say "add txtDomFacsoleqtr to the grid and alias
it
DomQtr" The control txtDomFacsoleqtr is in the detail section of the
report and

I'm not quite sure what you mean by "alias" Is that use Domqtr as the name
of the control as distinct from txtDomFacsoleqtr?

The control txtMonthlabel is also in the detail, how do I set its
"Criteria" in the properties?

The control which contains the expression is called prevqtramount and when
I
run the report I get an input box asking me to complete this value.
It's
as
if the calculation =Sum([prevqtramount]) in the footer can't find the
control in the detail.

I apologise for my ignorance here but really appreciate your input and
help
Thanks again
Tony

Since the DLookup() returns the same exact value for every record in your
report, I would
- add tblMainTabs to the report's record source
- add txtDomFacsoleqtr to the grid and alias it DomQtr
- add txtMonthlabel to the grid and set its criteria to txtqtr1
In your report, you and then use DomQtr rather than the DLookup().

--
Duane Hookom
MS Access MVP
--

I have a calculated control in the detail section of a report. The
calculation is

=[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthla
bel]= [txtqtr1]"))
The control is called prevqtramount. In a footer I want to calculate
the
sum
of this and have used =Sum([prevqtramount]) but nothing shows. Ive
tried
repeating the calculation and used

=Sum([txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMon
thlabel]= [txtqtr1]"))) but that doesn't work either.

What am I doing wrong?
Thanks
Tony