• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Can't Sum On the Report Footer (1 Viewer)

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
Hello Friends, Need your help!
Something wrong, Can't Sum On the Report Footer
In the detail I have Unbound Text-box "LossMoney" With Expression =Nz([Price])*Nz([Qt3]) working ok giving the correct results.
Now at the footer of the report I want to sum the "LossMoney"
Tried many variants, but not worked
 

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
Hi Bobo, Thanks for the answer...
I tried it but not worked, does nothing
 

boblarson

Smeghead
Local time
Today, 12:14
Joined
Jan 12, 2001
Messages
32,068
Hi Bobo, Thanks for the answer...
I tried it but not worked, does nothing

Did you make sure that there is no text box on the report named Price nor is there one named Qt3?

Also, have you made sure that you put it in the REPORT FOOTER and not accidentally in the PAGE FOOTER?
 

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
Hello Bob!
On the Report in Details I have Text-Box "Price" ,Text-Box QT3 and LossMoney Where I put my expression, it does the calculation.
then On the Footer of the report i created one unbound text box "TotalSum" and needed to sum the results which gave "LossMoney"
 

vbaInet

AWF VIP
Local time
Today, 19:14
Joined
Jan 22, 2010
Messages
26,374
Like Bob asked, did you put the TotalSum textbox in the Group Footer, Page Footer or the Report Footer section?

And did you enter the expression exactly as Bob gave you in post #2?

Plus, when you say it's not working, is it displaying an error or it's just blank?
 

Brianwarnock

Retired
Local time
Today, 19:14
Joined
Jun 2, 2003
Messages
12,701
Did you make sure that there is no text box on the report named Price nor is there one named Qt3?

Also, have you made sure that you put it in the REPORT FOOTER and not accidentally in the PAGE FOOTER?


Notice the emphasis on No in the first sentence, I'm sure that Bob did that for a reason.

Brian
 

boblarson

Smeghead
Local time
Today, 12:14
Joined
Jan 12, 2001
Messages
32,068
You need to use the FIELD names (from your record source) in the calculations and those field names CANNOT, I repeat, CANNOT be used as the names of text boxes on the report or else it WILL NOT WORK.
 

Brianwarnock

Retired
Local time
Today, 19:14
Joined
Jun 2, 2003
Messages
12,701
So the question he will(should) ask is why did it work in the detail section, is it when functions e.g. Sum are used that confusion is caused?

Brian
 

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
Like Bob asked, did you put the TotalSum textbox in the Group Footer, Page Footer or the Report Footer section?

And did you enter the expression exactly as Bob gave you in post #2?

Plus, when you say it's not working, is it displaying an error or it's just blank?

- I Put ToTalSum Unbound Text Box on the report footer
- I entered The expression exactly as Bob gave me
- No error, But Saying: Enter parameter Value for Price, Enter Parameter Value For QT3 and when the report is opened it displays zero "0"
 

boblarson

Smeghead
Local time
Today, 12:14
Joined
Jan 12, 2001
Messages
32,068
So the question he will(should) ask is why did it work in the detail section, is it when functions e.g. Sum are used that confusion is caused?

Brian

While technically using control references will SOMETIMES work in a report, you want to use the Fields instead of the controls. It is the fields which get aggregated and such so it is really needed to use them in the calculations and not the control names. This is especially important when we get to the footer and the aggregation of the data from above.
 

boblarson

Smeghead
Local time
Today, 12:14
Joined
Jan 12, 2001
Messages
32,068
- I Put ToTalSum Unbound Text Box on the report footer
- I entered The expression exactly as Bob gave me
- No error, But Saying: Enter parameter Value for Price, Enter Parameter Value For QT3 and when the report is opened it displays zero "0"

What is the Actual names for the price and QT3 fields in the TABLE/QUERY? You would refer to THOSE and NOT the name of the controls on the report. This is also the case in the calculation in the Details section. And if the controls are named exactly the same as the Fields that they are bound to (or any other fields in the report's record source for that matter) then rename them to something like txtPrice and txtQT3, but then use the FIELD names and NOT the control names in the calculations, both in the Detail Section and in the Footer.
 

Brianwarnock

Retired
Local time
Today, 19:14
Joined
Jun 2, 2003
Messages
12,701
While technically using control references will SOMETIMES work in a report, you want to use the Fields instead of the controls. It is the fields which get aggregated and such so it is really needed to use them in the calculations and not the control names. This is especially important when we get to the footer and the aggregation of the data from above.

So nothing to do with functions just a bit of pot luck,
If you, ie me, have always done it correctly then you don't learn the nuances, you have to make mistakes to really learn so that you can help others. :eek: :D

Brian
 

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
Hello Bob!
Actual names are: For The Price is Unitprice, but QT3 is Unbound text box with expression in it
 

boblarson

Smeghead
Local time
Today, 12:14
Joined
Jan 12, 2001
Messages
32,068
What is the expression and why can't it be done in the underlying query? I'm afraid an unbound text box isn't going to give you what you are wanting. So the answer is to move whatever is in the unbound text box expression to the query.
 

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
In QT3 expression I use is:
=DCount("*","DATA","Product = """ & [Product] & """ And Brand = """ & [Brand] & """ And Model = """ & [Model] & """ And Dateofpurchase = #" & [Dateofpurchase] & "# AND Status In ('WriteOff')")
 

boblarson

Smeghead
Local time
Today, 12:14
Joined
Jan 12, 2001
Messages
32,068
You might have to modify it slightly to work in the query but put it in the query as and this should work. Then you can use it in the report as the field name QT3:

QT3:DCount("*","DATA","Product = " & Chr(34) & [Product] & Chr(34) & " And Brand = " & Chr(34) & [Brand] & Chr(34) & " And Model = " & Chr(34) & [Model] & Chr(34) & " And Dateofpurchase = #" & [Dateofpurchase] & "# AND Status In ('WriteOff')")
 

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
Thanks Bob for the answer,
Sorry but I can't understood this, how to put this in a query?

I wonder if I can count the records of LossMoney in the footer of the report why cant sum them?
 

boblarson

Smeghead
Local time
Today, 12:14
Joined
Jan 12, 2001
Messages
32,068
Thanks Bob for the answer,
Sorry but I can't understood this, how to put this in a query?

You copy what I gave you in like this:



I wonder if I can count the records of LossMoney in the footer of the report why cant sum them?

Because there is no concept of a record there with the expression. Each row shows because it does the expression right then and there. But there is no aggregating available because the expression is NOT a field. The count is fine because you do have one of the fields there (price).
 

Attachments

  • vdaneliaquery01.png
    vdaneliaquery01.png
    5.7 KB · Views: 520

vdanelia

Registered User.
Local time
Today, 12:14
Joined
Jan 29, 2011
Messages
215
Waw! Coollllllllllll
Greatest Thanks Bob! You're Super
Works Perfectly

P.S. This was also a greatest lesson for me... I thought and this is a real good thing So handy and so needy.....
 

Users who are viewing this thread

Top Bottom