Total values in report footer (1 Viewer)

AccessWorld

New member
Local time
Today, 07:00
Joined
Jun 23, 2020
Messages
27
My report footer is not recognizing the values in a text box that I populate from a combobox using the [field].column(1) formula. I get a text result in the text box but I wish to sum, or count, by how many times a particular value comes up. The value is not stored in a field but displays in the report based on the combobox

=[acnumber].column(1) formula.

I refer to the populated column text box that shows the aircraft model type in my report detail section by attempting to add up each time a particular aircraft model string shows up.

So...aircraft number is the stored [acnumber] table field on the report. The aircraft model number (747-400) is determined by the column (1) function associated with aircraft tail number.

...example aircraft number is n123QZ (not real) combobox also has the aircraft model and ID. [ACNumber].Column(1) returns the correct 747-400 model/type.

I want to total each aircraft model that shows up in the detail section in the report footer. All formulas I have tried do not appear to be able to total a text box populated in this manner and not stored as a field.

Results hoped for:
Model Total
MD-11F 5
747-400 4
767-300 10
757 25

Perhaps there is a way to save a text bix populated in this way to a field for that same record? Then I might be able to easily calculate this info in the report footer?

Any ideas are most welcome?
Thanks
Stuck in MEM
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:00
Joined
Oct 29, 2018
Messages
14,098
Hi. Did you say you have a Combobox in a report? Why? How are you populating this Combobox? Aren't those values stored in a table?
 

arnelgp

error reading drive A:
Local time
Today, 21:00
Joined
May 7, 2009
Messages
11,514
can you use DCount()?
 

AccessWorld

New member
Local time
Today, 07:00
Joined
Jun 23, 2020
Messages
27
Hi. Did you say you have a Combobox in a report? Why? How are you populating this Combobox? Aren't those values stored in a table?
Yes....because it allows me to do this to select the aircraft model

=[combo45ACNumber].[column](1)
The model number is not part of the query (for now...can I populate the AC model in the query?.
 

AccessWorld

New member
Local time
Today, 07:00
Joined
Jun 23, 2020
Messages
27
Got it...ditched the combo. Combined two different tables in query. All is good now in calculations. Didnt think about...your comment/question did the trick...thank you from across the ocean. Hope all in your family is doing ok during COVID. dave
 

AccessWorld

New member
Local time
Today, 07:00
Joined
Jun 23, 2020
Messages
27
Is there a way to iterate through each model type to obtain sums for each without having to write a sum formula for each aircraft type?
 

arnelgp

error reading drive A:
Local time
Today, 21:00
Joined
May 7, 2009
Messages
11,514
you create another Total Query that will Count your aircraft by Type:
create a report for this Query.
add the New report as sub-report to your Current report.
 
Last edited:

AccessWorld

New member
Local time
Today, 07:00
Joined
Jun 23, 2020
Messages
27
Used a cross tab query in a subreport. All is well...thank you all!
 

Users who are viewing this thread

Top Bottom