Unbound Textbox on Access Report footer Question

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
HI
I have an unbound textbox (text24) on the detail part of a report with the formula =IIf([Quantity]=200,[Price],Null) where both Quantity and Price are field names. This works ok. In a footer section I want to sun this textbox. So I place another unbound textbox in the footer section with this formula =Sum([Text24]!value). When I run the report I get a prompt wanting me to key in Text24. Why will my report not recognize text24?

Thanks
L
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hi Liz

Try either of the following instead :

=Sum(IIf([Quantity]=200,[Price],Null)

or
=Sum([Text24])

HTH, Andrew :)
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
Hi Andrew

I did end up using =Sum(IIf([Quantity]=200,[Price],Null)

but my initial plan was to use =Sum([Text24]) but it would not work and I am very curious why the value of text24 is not been recognized.

L
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hi Liz
Possibly the null values prevent it from displaying the result.
Try something like this :
=Sum(Nz([Text24]))
Andrew :)
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

I think it's just one of Access' little glitches. If you create a calculated field in a query or report, and then want to base another calc on the first one, you can't just reference it. You need to use the whole calculation again -- though it's just a copy - paste job.

Denis
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
Thanks Andrew I did try =Sum(Nz([Text24])) but when I run the report it still prompts me for Text24 .

Denis I think you are right. I have tried many variations and every time I get the prompt asking for Text24


Thanks
L
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
I realise your question has been answered Liz but the 'glitch' thing doesn't sit well. I'd be interested to hear opinions / comments as to why this doesn't work because I can often get similar situations to work (using Access 2000 and 2003). Except in your case Liz I get the same error when I try my nz(...) suggestion....

So when can I get this to work? For example, in the detail section of a crosstab report, I have a calculated field being =Sum(Nz([Month1]))+ .... +Sum(Nz([Month12])) and I assign a name 'Subtotal'. I can then sum the individual month records in the report footer e.g. Sum(Nz([Month1])) and assign each a unique name like 'TotalJan' through to 'TotalDec'.

So far I haven't done anything flash but with the calculated totals I can then do 2 things : I can create a grand total in the report footer using the formula =[TotalJan]+ ... +[TotalDec] and assign the name 'GrandTotal'. This works fine and it sums the 12 calculated fields. I can then also create a new field in the detail section which is based on =[Subtotal]/[GrandTotal] and I can get the % for each record. This has used 2 calculated values to work out the %.

So I guess I don't understand why I can base calculations on calculations within a report (to get the %) but we can't do a simple =Sum([Text24])???? :unsure:

Any insight, thoughts or comments would be appreciated, because things like this really bug me (pun intended :wink:).

Andrew :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,995
Messages
5,834,779
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top