IIF function in Report Footer

  • Thread starter Thread starter pepenacho
  • Start date Start date
P

pepenacho

Hello:

I can't get an IIF function to work in the Access report footer. I can't
figure out why, I've written many of these and this one refuses to work.

Background:

I have two queries.
Qry-A has all the details for categories and amounts.

Let's say these are the fields in Qry-A:
Cat Amt1 Amt2 Amt3

Qry-B is Qry-A that is grouped by category and the amounts are summed up.
Instead of 500 lines (Qry-A) I have a nifty 12 lines (Qry-B).

Let's say these are the fields in Qry-B:
Cat SumOfAmt1 SumOfAmt2 SumOfAmt3

I wrote a report driven off of Qry-B (the 12 liner).

The report displays amts. for all 12 lines, and in the report footer I have
grand totals for each field (so sums for amounts in each field 1-3, where 12
lines are summed up into 1 number)

One category is special.

I want to flesh out that one category and display it separately in the
report footer.

In the report writer, I tried using various forms of

=IIF([Qry-B]!Cat = "ABC", SUM([Qry-B]!SumOfAmt1))

the control box in the report will simply not take it. I tried tweaking it
=IIF([Qry-B].Cat = "ABC", SUM([Qry-B].SumOfAmt1))
=IIF([Qry-B]!Cat Like "*ABC", SUM([Qry-B]!SumOfAmt1))
etc etc....

I even wrote a query just to get that one total and map it via the function
dialogue box .... nothing.

Nothing works.... I don't get it..

Any ideas?
 
Try [query-b.cat]. Your queryname and column name need to be in brackets
separated by a period.
 
You shouldn't need to include the name of the query.
Try something like:
=Sum(Abs([Cat] = "ABC") * [SumOfAmt1])

--
Duane Hookom
Microsoft Access MVP


Golfinray said:
Try [query-b.cat]. Your queryname and column name need to be in brackets
separated by a period.

pepenacho said:
Hello:

I can't get an IIF function to work in the Access report footer. I can't
figure out why, I've written many of these and this one refuses to work.

Background:

I have two queries.
Qry-A has all the details for categories and amounts.

Let's say these are the fields in Qry-A:
Cat Amt1 Amt2 Amt3

Qry-B is Qry-A that is grouped by category and the amounts are summed up.
Instead of 500 lines (Qry-A) I have a nifty 12 lines (Qry-B).

Let's say these are the fields in Qry-B:
Cat SumOfAmt1 SumOfAmt2 SumOfAmt3

I wrote a report driven off of Qry-B (the 12 liner).

The report displays amts. for all 12 lines, and in the report footer I have
grand totals for each field (so sums for amounts in each field 1-3, where 12
lines are summed up into 1 number)

One category is special.

I want to flesh out that one category and display it separately in the
report footer.

In the report writer, I tried using various forms of

=IIF([Qry-B]!Cat = "ABC", SUM([Qry-B]!SumOfAmt1))

the control box in the report will simply not take it. I tried tweaking it
=IIF([Qry-B].Cat = "ABC", SUM([Qry-B].SumOfAmt1))
=IIF([Qry-B]!Cat Like "*ABC", SUM([Qry-B]!SumOfAmt1))
etc etc....

I even wrote a query just to get that one total and map it via the function
dialogue box .... nothing.

Nothing works.... I don't get it..

Any ideas?
Click to expand...
Click to expand...
 
That =Sum(Abs([Cat] = "ABC") * [SumOfAmt1]) worked very well, thank you.

If you don't mind, I have a few follow up questions:

1) The solution that you gave me... I've never seen that syntax, what is it,
an array?
It sure feels like one. I've never done it in Access, but I do them in Excel
all the time.

2) If I remove the "Abs" function (absolute value), why does your syntax
flip the natural +/- sign of the value... weird (I have positives and
negatives in my tbls).

3) I've tried looking this syntax up, can't find it, was this in MSoft's
X-Files?

4) Why does the report writer in Access preface all the fields with the
[name of query]! if it's clearly not needed. The only reason I could think of
is if one wanted to drop in a value from another query, one that is not
mapped as the data source for the report...but when I tried doing that this
fails.

Sorry for the questions, it's all very mundane but useful stuff.

Robert (Pepe)


Duane Hookom said:
You shouldn't need to include the name of the query.
Try something like:
=Sum(Abs([Cat] = "ABC") * [SumOfAmt1])

--
Duane Hookom
Microsoft Access MVP


Golfinray said:
Try [query-b.cat]. Your queryname and column name need to be in brackets
separated by a period.

pepenacho said:
Hello:

I can't get an IIF function to work in the Access report footer. I can't
figure out why, I've written many of these and this one refuses to work.

Background:

I have two queries.
Qry-A has all the details for categories and amounts.

Let's say these are the fields in Qry-A:
Cat Amt1 Amt2 Amt3

Qry-B is Qry-A that is grouped by category and the amounts are summed up.
Instead of 500 lines (Qry-A) I have a nifty 12 lines (Qry-B).

Let's say these are the fields in Qry-B:
Cat SumOfAmt1 SumOfAmt2 SumOfAmt3

I wrote a report driven off of Qry-B (the 12 liner).

The report displays amts. for all 12 lines, and in the report footer I have
grand totals for each field (so sums for amounts in each field 1-3, where 12
lines are summed up into 1 number)

One category is special.

I want to flesh out that one category and display it separately in the
report footer.

In the report writer, I tried using various forms of

=IIF([Qry-B]!Cat = "ABC", SUM([Qry-B]!SumOfAmt1))

the control box in the report will simply not take it. I tried tweaking it
=IIF([Qry-B].Cat = "ABC", SUM([Qry-B].SumOfAmt1))
=IIF([Qry-B]!Cat Like "*ABC", SUM([Qry-B]!SumOfAmt1))
etc etc....

I even wrote a query just to get that one total and map it via the function
dialogue box .... nothing.

Nothing works.... I don't get it..

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
1) The solution that you gave me... I've never seen that syntax, what is it,
an array?
It sure feels like one. I've never done it in Access, but I do them in Excel
all the time.
A) Start with the Abs(expression) where the expression returns true/-1 or
false/0 for each record and table the (Abs)olute value. Then multiply 1 or 0
by the value to sum.

2) If I remove the "Abs" function (absolute value), why does your syntax
flip the natural +/- sign of the value... weird (I have positives and
negatives in my tbls).
A) Abs() is absolute value which great with expressions that evaluate to
true or false

3) I've tried looking this syntax up, can't find it, was this in MSoft's
X-Files?
A) It's a bit unconventional but uses a couple simple principles

4) Why does the report writer in Access preface all the fields with the
[name of query]! if it's clearly not needed. The only reason I could think of
is if one wanted to drop in a value from another query, one that is not
mapped as the data source for the report...but when I tried doing that this
fails.
A) The report writer would only need to add the name of the query if there
were duplicate field names. You can't drop in a field name that isn't in the
report's record source.
--
Duane Hookom
Microsoft Access MVP
Sorry for the questions, it's all very mundane but useful stuff.

Robert (Pepe)


Duane Hookom said:
You shouldn't need to include the name of the query.
Try something like:
=Sum(Abs([Cat] = "ABC") * [SumOfAmt1])

--
Duane Hookom
Microsoft Access MVP


Golfinray said:
Try [query-b.cat]. Your queryname and column name need to be in brackets
separated by a period.

:

Hello:

I can't get an IIF function to work in the Access report footer. I can't
figure out why, I've written many of these and this one refuses to work.

Background:

I have two queries.
Qry-A has all the details for categories and amounts.

Let's say these are the fields in Qry-A:
Cat Amt1 Amt2 Amt3

Qry-B is Qry-A that is grouped by category and the amounts are summed up.
Instead of 500 lines (Qry-A) I have a nifty 12 lines (Qry-B).

Let's say these are the fields in Qry-B:
Cat SumOfAmt1 SumOfAmt2 SumOfAmt3

I wrote a report driven off of Qry-B (the 12 liner).

The report displays amts. for all 12 lines, and in the report footer I have
grand totals for each field (so sums for amounts in each field 1-3, where 12
lines are summed up into 1 number)

One category is special.

I want to flesh out that one category and display it separately in the
report footer.

In the report writer, I tried using various forms of

=IIF([Qry-B]!Cat = "ABC", SUM([Qry-B]!SumOfAmt1))

the control box in the report will simply not take it. I tried tweaking it
=IIF([Qry-B].Cat = "ABC", SUM([Qry-B].SumOfAmt1))
=IIF([Qry-B]!Cat Like "*ABC", SUM([Qry-B]!SumOfAmt1))
etc etc....

I even wrote a query just to get that one total and map it via the function
dialogue box .... nothing.

Nothing works.... I don't get it..

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane, I have a similar question on footer sums;

I have put a sum on the footer to calculate the total length.
=sum([totallength])
I have a 'Completed Date' column on my report, is there a way I can get a
sum to calculate the total length only for the data with a completed date?

and finally a sum to give me the remaining length ie total length - the data
with a completed date?

Your help would be very much appreciated

Yours Aye

Bruce

Duane Hookom said:
You shouldn't need to include the name of the query.
Try something like:
=Sum(Abs([Cat] = "ABC") * [SumOfAmt1])

--
Duane Hookom
Microsoft Access MVP


Golfinray said:
Try [query-b.cat]. Your queryname and column name need to be in brackets
separated by a period.

pepenacho said:
Hello:

I can't get an IIF function to work in the Access report footer. I can't
figure out why, I've written many of these and this one refuses to work.

Background:

I have two queries.
Qry-A has all the details for categories and amounts.

Let's say these are the fields in Qry-A:
Cat Amt1 Amt2 Amt3

Qry-B is Qry-A that is grouped by category and the amounts are summed up.
Instead of 500 lines (Qry-A) I have a nifty 12 lines (Qry-B).

Let's say these are the fields in Qry-B:
Cat SumOfAmt1 SumOfAmt2 SumOfAmt3

I wrote a report driven off of Qry-B (the 12 liner).

The report displays amts. for all 12 lines, and in the report footer I have
grand totals for each field (so sums for amounts in each field 1-3, where 12
lines are summed up into 1 number)

One category is special.

I want to flesh out that one category and display it separately in the
report footer.

In the report writer, I tried using various forms of

=IIF([Qry-B]!Cat = "ABC", SUM([Qry-B]!SumOfAmt1))

the control box in the report will simply not take it. I tried tweaking it
=IIF([Qry-B].Cat = "ABC", SUM([Qry-B].SumOfAmt1))
=IIF([Qry-B]!Cat Like "*ABC", SUM([Qry-B]!SumOfAmt1))
etc etc....

I even wrote a query just to get that one total and map it via the function
dialogue box .... nothing.

Nothing works.... I don't get it..

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top