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 |
#11
|
|||
|
|||
Please Help - Need to finish this project
On Apr 8, 5:55*pm, teelee wrote:
On my Form I have all controls as Currancy The Field names I have are Cost of Plan, Web Site Charge, EPlan Charge, TPM Charge, AuthNetCharge. These field names are all the same names on my subform. Can you explain more about the operator? I would enter the Cost of Plans,Eplan Charge,TPMCharge, AuthNetCharge and would like them to total in the Total Revenue. It would go this way: Cost of Plans minus EPlanCharge minus the TPMCharge minus the AuthNetCharge = Total Revenue. Same would go if the Plan was purchased by WebSite. Any suggestions on this is very much appreciated. Thanks The 'operator' is ' - ' (minus, or subtract). You have both 'Cost of Plans' and 'Cost of Plan' so I'm not positive I've chosen the correct spelling. I'm breaking this into pieces to make it easier to see, but you will need to enter it all on one line. Try: TotalRevenue: IIF(SoldWeb='N', [Cost of Plans] - [EPlanCharge] - [TPMCharge] - [AuthNetCharge], [Cost of Plans] - [Web Site Charge] - [TPMCharge] - [AuthNetCharge]) -- Clif |
#12
|
|||
|
|||
Please Help - Need to finish this project
Ok that worked the only thing now is after I run it I get Enter a Parameter
Value with SoldWeb and then WebSiteCharge. If I don't enter anything the TotalRevenue field is blank. Thanks "Clif McIrvin" wrote: On Apr 8, 5:55 pm, teelee wrote: On my Form I have all controls as Currancy The Field names I have are Cost of Plan, Web Site Charge, EPlan Charge, TPM Charge, AuthNetCharge. These field names are all the same names on my subform. Can you explain more about the operator? I would enter the Cost of Plans,Eplan Charge,TPMCharge, AuthNetCharge and would like them to total in the Total Revenue. It would go this way: Cost of Plans minus EPlanCharge minus the TPMCharge minus the AuthNetCharge = Total Revenue. Same would go if the Plan was purchased by WebSite. Any suggestions on this is very much appreciated. Thanks The 'operator' is ' - ' (minus, or subtract). You have both 'Cost of Plans' and 'Cost of Plan' so I'm not positive I've chosen the correct spelling. I'm breaking this into pieces to make it easier to see, but you will need to enter it all on one line. Try: TotalRevenue: IIF(SoldWeb='N', [Cost of Plans] - [EPlanCharge] - [TPMCharge] - [AuthNetCharge], [Cost of Plans] - [Web Site Charge] - [TPMCharge] - [AuthNetCharge]) -- Clif |
#13
|
|||
|
|||
Please Help - Need to finish this project
On Tue, 8 Apr 2008 17:37:13 -0700, teelee
wrote: Ok that worked the only thing now is after I run it I get Enter a Parameter Value with SoldWeb and then WebSiteCharge. If I don't enter anything the TotalRevenue field is blank. If you get a prompt for [SoldWeb] it means that there is no field in the query of that name. Are you certain of the spelling? [Sold Web] and [SoldWeb] are two different names... -- John W. Vinson [MVP] |
#14
|
|||
|
|||
Please Help - Need to finish this project
I don't have a field named SoldWeb I'm not sure how that even got in here. On
my Form I have all controls as Currancy The Field names I have are Cost of Plans, WebSiteCharge, EPlanCharge, TPMCharge, AuthNetCharge. Thanks "John W. Vinson" wrote: On Tue, 8 Apr 2008 17:37:13 -0700, teelee wrote: Ok that worked the only thing now is after I run it I get Enter a Parameter Value with SoldWeb and then WebSiteCharge. If I don't enter anything the TotalRevenue field is blank. If you get a prompt for [SoldWeb] it means that there is no field in the query of that name. Are you certain of the spelling? [Sold Web] and [SoldWeb] are two different names... -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Please Help - Need to finish this project
I know have it entered like this: Total:
IIf([TotalRevenue]='N',[CostofPlans]-[EPlanCharge]-[TPMCharge]-[AuthNetCharge],-[WebCharge]-[TPMCharge]-[AuthNetCharge]) I'm using the minus but some unknown reason it's doing addition on some fields and nothing on others. I'm really trying to finish this project and would appreciate any help with this. Thanks "John W. Vinson" wrote: On Tue, 8 Apr 2008 17:37:13 -0700, teelee wrote: Ok that worked the only thing now is after I run it I get Enter a Parameter Value with SoldWeb and then WebSiteCharge. If I don't enter anything the TotalRevenue field is blank. If you get a prompt for [SoldWeb] it means that there is no field in the query of that name. Are you certain of the spelling? [Sold Web] and [SoldWeb] are two different names... -- John W. Vinson [MVP] |
#16
|
|||
|
|||
Please Help - Need to finish this project
Look right here in your expression:
[AuthNetCharge],-[WebCharge] see the comma, that's telling the IIf expression to start the last argument (the false part) right there. Everything following that comma in your expression is negative. I suggest that you start the column over and add a little bit to each argument, like: Total: IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) then go to: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),[WebCharge]) then: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMCharge])) and keep going and testing until you get the entire expression. If you have any problem, it will be easy to find. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "teelee" wrote in message ... I know have it entered like this: Total: IIf([TotalRevenue]='N',[CostofPlans]-[EPlanCharge]-[TPMCharge]-[AuthNetCharge],-[WebCharge]-[TPMCharge]-[AuthNetCharge]) I'm using the minus but some unknown reason it's doing addition on some fields and nothing on others. I'm really trying to finish this project and would appreciate any help with this. Thanks "John W. Vinson" wrote: On Tue, 8 Apr 2008 17:37:13 -0700, teelee wrote: Ok that worked the only thing now is after I run it I get Enter a Parameter Value with SoldWeb and then WebSiteCharge. If I don't enter anything the TotalRevenue field is blank. If you get a prompt for [SoldWeb] it means that there is no field in the query of that name. Are you certain of the spelling? [Sold Web] and [SoldWeb] are two different names... -- John W. Vinson [MVP] |
#17
|
|||
|
|||
Please Help - Need to finish this project
Total: IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) When I enter this
expresssion I get only the what the WebCharge is. When I enter this one I'm getting the same thing above Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),[WebCharge]) When I enter this one I'm getting the WebCharge minus the TPM Charge Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMCharge])) I'm just not sure what I'm doing wrong, I need the Costof Plans to minus the Eplan charge -TPMCharge and - AuthNetCharge if purchased by Eplan. If it's purchased by WebSite then I need it this way WebSite - TPM Charge - AuthNetCharge. Thanks in advance "Arvin Meyer [MVP]" wrote: Look right here in your expression: [AuthNetCharge],-[WebCharge] see the comma, that's telling the IIf expression to start the last argument (the false part) right there. Everything following that comma in your expression is negative. I suggest that you start the column over and add a little bit to each argument, like: Total: IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) then go to: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),[WebCharge]) then: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMCharge])) and keep going and testing until you get the entire expression. If you have any problem, it will be easy to find. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "teelee" wrote in message ... I know have it entered like this: Total: IIf([TotalRevenue]='N',[CostofPlans]-[EPlanCharge]-[TPMCharge]-[AuthNetCharge],-[WebCharge]-[TPMCharge]-[AuthNetCharge]) I'm using the minus but some unknown reason it's doing addition on some fields and nothing on others. I'm really trying to finish this project and would appreciate any help with this. Thanks "John W. Vinson" wrote: On Tue, 8 Apr 2008 17:37:13 -0700, teelee wrote: Ok that worked the only thing now is after I run it I get Enter a Parameter Value with SoldWeb and then WebSiteCharge. If I don't enter anything the TotalRevenue field is blank. If you get a prompt for [SoldWeb] it means that there is no field in the query of that name. Are you certain of the spelling? [Sold Web] and [SoldWeb] are two different names... -- John W. Vinson [MVP] |
#18
|
|||
|
|||
Please Help - Need to finish this project
Teelee, Arvin is suggesting that you build (and test) your expression
one term at a time so that you can easily discover exactly what is causing your error. How exactly do you expect Access to know whether this is a Web Purchase or not? That is the purpose of using the Immediate If (IIF) function, but you need to tell Access how to test for a Web sale. Are you typing the formulas into these posts? It would be better for you to do a windows copy and paste, then we can see exactly what you have. -- Clif On Apr 9, 8:36*am, teelee wrote: Total: IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) When I enter this expresssion I get only the what the WebCharge is. When I enter this one I'm getting the same thing above Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),[WebCharge]) When I enter this one I'm getting the WebCharge minus the TPM Charge * Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMCharg*e])) I'm just not sure what I'm doing wrong, I need the Costof Plans to minus the Eplan charge -TPMCharge and - AuthNetCharge if purchased by Eplan. If it's purchased by WebSite then I need it this way WebSite - TPM Charge - AuthNetCharge. Thanks in advance "Arvin Meyer [MVP]" wrote: Look right here in your expression: [AuthNetCharge],-[WebCharge] see the comma, that's telling the IIf expression to start the last argument (the false part) right there. Everything following that comma in your expression is negative. I suggest that you start the column over and add a little bit to each argument, like: Total: IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) then go to: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),[WebCharge]) then: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMCharg*e])) and keep going and testing until you get the entire expression. If you have any problem, it will be easy to find. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
#19
|
|||
|
|||
Please Help - Need to finish this project
The expressions that you sent I had copied and pasted into the Query. So do I
need to build these expressions or copy and paste the expressions that you sent previous? Thanks "Clif McIrvin" wrote: Teelee, Arvin is suggesting that you build (and test) your expression one term at a time so that you can easily discover exactly what is causing your error. How exactly do you expect Access to know whether this is a Web Purchase or not? That is the purpose of using the Immediate If (IIF) function, but you need to tell Access how to test for a Web sale. Are you typing the formulas into these posts? It would be better for you to do a windows copy and paste, then we can see exactly what you have. -- Clif On Apr 9, 8:36 am, teelee wrote: Total: IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) When I enter this expresssion I get only the what the WebCharge is. When I enter this one I'm getting the same thing above Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),[WebCharge]) When I enter this one I'm getting the WebCharge minus the TPM Charge Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMChargÂ*e])) I'm just not sure what I'm doing wrong, I need the Costof Plans to minus the Eplan charge -TPMCharge and - AuthNetCharge if purchased by Eplan. If it's purchased by WebSite then I need it this way WebSite - TPM Charge - AuthNetCharge. Thanks in advance "Arvin Meyer [MVP]" wrote: Look right here in your expression: [AuthNetCharge],-[WebCharge] see the comma, that's telling the IIf expression to start the last argument (the false part) right there. Everything following that comma in your expression is negative. I suggest that you start the column over and add a little bit to each argument, like: Total: IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) then go to: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),[WebCharge]) then: Total: IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMChargÂ*e])) and keep going and testing until you get the entire expression. If you have any problem, it will be easy to find. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
#20
|
|||
|
|||
Please Help - Need to finish this project
On Wed, 9 Apr 2008 17:37:01 -0700, teelee
wrote: The expressions that you sent I had copied and pasted into the Query. So do I need to build these expressions or copy and paste the expressions that you sent previous? Teelee, bear in mind: You can see your database. You know your fieldnames. You can edit your expressions. Clif, Arvin and I cannot. We're volunteers, donating our time on these newsgroups; we're not Microsoft tech support, and we're not being paid to give you complete solutions. You will need to UNDERSTAND what the suggested code does, and if it applies to your situation, use it; if not, adapt it; if you can't figure out how to adapt it, you need to post enough information (what your fieldnames are, what expression you used, what result you wanted, and what result you got) that someone could help. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|