If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Repost Sum in a Report footer
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] |
#3
|
|||
|
|||
Repost Sum in a Report footer
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 |
#4
|
|||
|
|||
Repost Sum in a Report footer
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] |
#5
|
|||
|
|||
Repost Sum in a Report footer
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 |
#6
|
|||
|
|||
Repost Sum in a Report footer
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 |
#7
|
|||
|
|||
Repost Sum in a Report footer
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 |
#8
|
|||
|
|||
Repost Sum in a Report footer
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 |
#9
|
|||
|
|||
Repost Sum in a Report footer
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 |
Thread Tools | |
Display Modes | |
|
|