Unbound Textbox on Access Report footer Question

[Liz_I3]

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]

Hi Liz

Try either of the following instead :

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

or
=Sum([Text24])

HTH, Andrew

 

[Liz_I3]

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]

Hi Liz
Possibly the null values prevent it from displaying the result.
Try something like this :
=Sum(Nz([Text24]))
Andrew

 

[SydneyGeek]

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]

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]

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])????

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

Andrew

 

[SydneyGeek]

Hi Andrew,

Found this link that explains this little glitch -- http://support.microsoft.com/default.aspx?scid=KB;EN-US;207763

Apparently, it's because Access does not store calculated values so you (generally) need to repeat the calculation if you want to use it somewhere else in the form / report

Denis