Repost Sum in a Report footer

  • Thread starter Thread starter Elen
  • Start date Start date
E

Elen

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 said:
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.
Click to expand...


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])
 
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,...)
 
Thanks Marsh but I get an execution error: Expression =sum(IIF([paydate]="
","
",([agent_commisions]/100)*[cost_product])) not valid
Click to expand...
Click to expand...

Marshall Barton said:
Elen said:
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.
Click to expand...


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])
Click to expand...
 
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) said:
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,...)
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
Click to expand...
Click to expand...
 
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,"",...)


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) said:
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,...)
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
Click to expand...
Click to expand...
Click to expand...
 
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) said:
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,"",...)


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) said:
As a guess, Paydate is not a space, but is a null value or an actual
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Count your left and right parens and make sure they match appropriately.

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

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) said:
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,"",...)


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)" <[email protected]> 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
Click to expand...
Click to expand...
Click to expand...
 
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) said:
Count your left and right parens and make sure they match appropriately.

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

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) said:
Sorry, I'm unaware of what Cast will do with a field that is null when
Click to expand...
you
try
to cast it as a date. It may return a zero-length string (that is ""
Click to expand...
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)" <[email protected]> escribió en el mensaje
As a guess, Paydate is not a space, but is a null value or an
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top