Unbound Textbox on Access Report footer Question

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
586
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
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
586
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,432
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
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
586
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,432
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 :)
 

Forum statistics

Threads
1,077,866
Messages
5,336,857
Members
399,109
Latest member
gdcuk

Some videos you may like

This Week's Hot Topics

Top