Repost Sum in a Report footer

[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

 

[Marshall Barton]

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])

 

[John Spencer (MVP)]

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]

Thanks Marsh but I get an execution error: Expression =sum(IIF([paydate]="
","

",([agent_commisions]/100)*[cost_product])) not valid

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])

 

[Elen]

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

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

 

[John Spencer (MVP)]

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.

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

 

[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.

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.

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

 

[John Spencer (MVP)]

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.

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

 

[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.

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.

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)" <[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