Repost Sum in a Report footer

Hi,

I cannot seem to resolve a problem and I hope someone can help.



In the Detail section of a report in a field I use the following equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])



The Report is triggered by a form where an agent name is chosen and lists
orders and commissions during the month *if* the orders are paid.



Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.



What am I doing wrong?

TIA

Elen

Elen wrote:
I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen and lists
orders and commissions during the month *if* the orders are paid.

Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.


It should work. But, are you really storing a space
character in the PayDate field for unpaid orders???

Typically, you would just not enter anything in a field that
doesn't have a value and a Date/Time field would contain
Null, not a space. I would think that you need an
expression more like:

=IIF(IsNull(paydate),Null,([agent_commisions]/100)*[cost_product])

--
Marsh
MVP [MS Access]

As a guess, Paydate is not a space, but is a null value or an actual date. So
test for that.

IIF(IsNull([PayDate])),Null, ...)

or

IIF(IsDate([PayDate])=False,Null,...)

Elen wrote:

Hi,

I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen and lists
orders and commissions during the month *if* the orders are paid.

Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.

What am I doing wrong?

TIA

Elen

Thanks Marsh but I get an execution error: Expression =sum(IIF([paydate]="
","
",([agent_commisions]/100)*[cost_product])) not valid

"Marshall Barton" escribió en el mensaje
news
Elen wrote:
I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following
equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen and lists
orders and commissions during the month *if* the orders are paid.

Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.


It should work. But, are you really storing a space
character in the PayDate field for unpaid orders???

Typically, you would just not enter anything in a field that
doesn't have a value and a Date/Time field would contain
Null, not a space. I would think that you need an
expression more like:

=IIF(IsNull(paydate),Null,([agent_commisions]/100)*[cost_product])

--
Marsh
MVP [MS Access]

Paydate is stored in a SQL2k Server as varchar format yyyymmdd. I use
cast(paydate as smalldatetime) to convert and use it.

"John Spencer (MVP)" escribió en el mensaje
...
As a guess, Paydate is not a space, but is a null value or an actual date.
So
test for that.

IIF(IsNull([PayDate])),Null, ...)

or

IIF(IsDate([PayDate])=False,Null,...)

Elen wrote:

Hi,

I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following
equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen and
lists
orders and commissions during the month *if* the orders are paid.

Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.

What am I doing wrong?

TIA

Elen

Sorry, I'm unaware of what Cast will do with a field that is null when you try
to cast it as a date. It may return a zero-length string (that is "" NOT " " Or
it may return a space or it may return a null.

Also, when you say that your IIF doesn't work, you don't say what that means.
Do you get #error#, wrong dates, wrong values returned, all blanks?

Try testing the length of paydate as a string.

IIF(Len([PayDate]&"")=0,"",...)



Elen wrote:

Paydate is stored in a SQL2k Server as varchar format yyyymmdd. I use
cast(paydate as smalldatetime) to convert and use it.

"John Spencer (MVP)" escribió en el mensaje
...
As a guess, Paydate is not a space, but is a null value or an actual date.
So
test for that.

IIF(IsNull([PayDate])),Null, ...)

or

IIF(IsDate([PayDate])=False,Null,...)

Elen wrote:

Hi,

I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following
equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen and
lists
orders and commissions during the month *if* the orders are paid.

Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.

What am I doing wrong?

TIA

Elen

Ok I eliminated the *cast* syntax and I'm now using
IIF(IsNull([PayDate])),Null, ...)
which calculates the commission nicely but
=sum(IIF(IsNull([PayDate])),Null, ...) in the footer still doesn't work. I
get a VB error stating that =sum(IIF(IsNull([PayDate])),Null, ...) is an
invalid equation.

"John Spencer (MVP)" escribió en el mensaje
...
Sorry, I'm unaware of what Cast will do with a field that is null when you
try
to cast it as a date. It may return a zero-length string (that is "" NOT
" " Or
it may return a space or it may return a null.

Also, when you say that your IIF doesn't work, you don't say what that
means.
Do you get #error#, wrong dates, wrong values returned, all blanks?

Try testing the length of paydate as a string.

IIF(Len([PayDate]&"")=0,"",...)



Elen wrote:

Paydate is stored in a SQL2k Server as varchar format yyyymmdd. I use
cast(paydate as smalldatetime) to convert and use it.

"John Spencer (MVP)" escribió en el mensaje
...
As a guess, Paydate is not a space, but is a null value or an actual
date.
So
test for that.

IIF(IsNull([PayDate])),Null, ...)

or

IIF(IsDate([PayDate])=False,Null,...)

Elen wrote:

Hi,

I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following
equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen and
lists
orders and commissions during the month *if* the orders are paid.

Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.

What am I doing wrong?

TIA

Elen

Count your left and right parens and make sure they match appropriately.

SUM(IIF(IsNull(PayDate),0,[agent_commisions]/100)*[cost_product]))


Elen wrote:

Ok I eliminated the *cast* syntax and I'm now using
IIF(IsNull([PayDate])),Null, ...)
which calculates the commission nicely but
=sum(IIF(IsNull([PayDate])),Null, ...) in the footer still doesn't work. I
get a VB error stating that =sum(IIF(IsNull([PayDate])),Null, ...) is an
invalid equation.

"John Spencer (MVP)" escribió en el mensaje
...
Sorry, I'm unaware of what Cast will do with a field that is null when you
try
to cast it as a date. It may return a zero-length string (that is "" NOT
" " Or
it may return a space or it may return a null.

Also, when you say that your IIF doesn't work, you don't say what that
means.
Do you get #error#, wrong dates, wrong values returned, all blanks?

Try testing the length of paydate as a string.

IIF(Len([PayDate]&"")=0,"",...)



Elen wrote:

Paydate is stored in a SQL2k Server as varchar format yyyymmdd. I use
cast(paydate as smalldatetime) to convert and use it.

"John Spencer (MVP)" escribió en el mensaje
...
As a guess, Paydate is not a space, but is a null value or an actual
date.
So
test for that.

IIF(IsNull([PayDate])),Null, ...)

or

IIF(IsDate([PayDate])=False,Null,...)

Elen wrote:

Hi,

I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following
equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen and
lists
orders and commissions during the month *if* the orders are paid.

Now I would like to total the above field in the Report's footer (or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.

What am I doing wrong?

TIA

Elen

Reading the help pages I found out why my Sum of a calculated field doesn't
work.. It's due to the fact that the Report is based on a Query so, I'm
instructed to calculate the field in the Query and not in the Report.
But because in a Query the IIF syntax doesn't work (need to use Case) I may
post my Q: in the appropiate newsgroup.
Thank you all.


"John Spencer (MVP)" escribió en el mensaje
...
Count your left and right parens and make sure they match appropriately.

SUM(IIF(IsNull(PayDate),0,[agent_commisions]/100)*[cost_product]))


Elen wrote:

Ok I eliminated the *cast* syntax and I'm now using
IIF(IsNull([PayDate])),Null, ...)
which calculates the commission nicely but
=sum(IIF(IsNull([PayDate])),Null, ...) in the footer still doesn't work.
I
get a VB error stating that =sum(IIF(IsNull([PayDate])),Null, ...) is an
invalid equation.

"John Spencer (MVP)" escribió en el mensaje
...
Sorry, I'm unaware of what Cast will do with a field that is null when
you
try
to cast it as a date. It may return a zero-length string (that is ""
NOT
" " Or
it may return a space or it may return a null.

Also, when you say that your IIF doesn't work, you don't say what that
means.
Do you get #error#, wrong dates, wrong values returned, all blanks?

Try testing the length of paydate as a string.

IIF(Len([PayDate]&"")=0,"",...)



Elen wrote:

Paydate is stored in a SQL2k Server as varchar format yyyymmdd. I
use
cast(paydate as smalldatetime) to convert and use it.

"John Spencer (MVP)" escribió en el mensaje
...
As a guess, Paydate is not a space, but is a null value or an
actual
date.
So
test for that.

IIF(IsNull([PayDate])),Null, ...)

or

IIF(IsDate([PayDate])=False,Null,...)

Elen wrote:

Hi,

I cannot seem to resolve a problem and I hope someone can help.

In the Detail section of a report in a field I use the following
equation:

=IIF([paydate]=" "," ",([agent_commisions]/100)*[cost_product])

The Report is triggered by a form where an agent name is chosen
and
lists
orders and commissions during the month *if* the orders are
paid.

Now I would like to total the above field in the Report's footer
(or
wherever) but =sum(IIF([paydate]=" ","
",([agent_commisions]/100)*[cost_product]))

doesn't work.

What am I doing wrong?

TIA

Elen