Unbound Textbox on Access Report footer Question

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
595
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
595
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
595
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,082,278
Messages
5,364,202
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top