Please Help - Need to finish this project

[teelee]

I have a Form and a subform, on the Main form you would enter the Cost of a
Plan, along with the way that the plan was purchased. So I would have Cost of
Plan - EPlan Charge - Trip Charge - Auth Charge. I have a total revenue field
which I would like for the totals to be entered automatically. Now on the
subform the cost of plans along with the Eplan charge, trip charge, auth
charge would already be showing once the info is entered into the Main form.
I can't seem to figure out how to total these items on the Main Form again it
would be:
Cost Of Plans - EPlan Charge - Trip Charge - Auth Charge = Total Revenue.
Cost of Plans - Web Charge - Trip Charge - Auth Charge = Total Revenue
The other is just Direct Bill.

Does anyone have any suggestions, or is there another way of doing this?
Any help is very much appreicated. All the field are all seperated. I'm
thnking maybe I need to have them in a combo box as these are all purchase
methods, except Cost of Plans

Thanks

 

[Arvin Meyer [MVP]]

There are 2 basic ways to handle this:

1. You can create a column in a query which does the math.
2. You can create an unbound textbox on the form which has a controlsource
of:

= [Cost Of Plans] - [EPlan Charge] - [Trip Charge] - [Auth Charge]

 

[teelee]

Ok this is the expression I entered in the query:
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth))
now I'm getting this error message: The expression you entered contains
invalid syntax you may have entered a comma without a proceding value or
identifier.

Thanks

There are 2 basic ways to handle this:

1. You can create a column in a query which does the math.
2. You can create an unbound textbox on the form which has a controlsource
of:

= [Cost Of Plans] - [EPlan Charge] - [Trip Charge] - [Auth Charge]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a Form and a subform, on the Main form you would enter the Cost of a
Plan, along with the way that the plan was purchased. So I would have Cost
of
Plan - EPlan Charge - Trip Charge - Auth Charge. I have a total revenue
field
which I would like for the totals to be entered automatically. Now on the
subform the cost of plans along with the Eplan charge, trip charge, auth
charge would already be showing once the info is entered into the Main
form.
I can't seem to figure out how to total these items on the Main Form again
it
would be:
Cost Of Plans - EPlan Charge - Trip Charge - Auth Charge = Total Revenue.
Cost of Plans - Web Charge - Trip Charge - Auth Charge = Total Revenue
The other is just Direct Bill.

Does anyone have any suggestions, or is there another way of doing this?
Any help is very much appreicated. All the field are all seperated. I'm
thnking maybe I need to have them in a combo box as these are all purchase
methods, except Cost of Plans

Thanks

 

[Clif McIrvin]

Ok this is the expression I entered in the query:
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth))
now I'm getting this error message: The expression you entered contains
invalid syntax you may have entered a comma without a proceding value or
identifier.

Thanks

From memory, IIF requires 3 parts, and your expression shows only 2.
You *must*
provide an expression for the FALSE case.

 

[teelee]

Not really sure what that means, can you help me out a little bit more?

Thanks

Ok this is the expression I entered in the query:
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth))
now I'm getting this error message: The expression you entered contains
invalid syntax you may have entered a comma without a proceding value or
identifier.

Thanks

From memory, IIF requires 3 parts, and your expression shows only 2.
You *must*
provide an expression for the FALSE case.

 

[teelee]

On my form the person would enter the Cost of Plan along with the EPlan
Charge if purchased in that way, or Web Site, again if purchased in that way.
Would also enter the Trip Charge and the Auth Charge. What I'm trying to do
for the Total field is have this calculated after this info above is entered,
in other words if the Plan was purchased by Website then it would be Cost of
Plan ($2500.00)-WebSite Charge $1500.00) - Trip Charge - Auth Charge = Total
Revenue. I'm thinking this is the way to do this. Any other suggestions would
be very much appreciated as I'm trying real hard to finish this project.

Thanks

Ok this is the expression I entered in the query:
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth))
now I'm getting this error message: The expression you entered contains
invalid syntax you may have entered a comma without a proceding value or
identifier.

Thanks

From memory, IIF requires 3 parts, and your expression shows only 2.
You *must*
provide an expression for the FALSE case.

 

[Arvin Meyer [MVP]]

Immediate If (IIf) requires an evaluation of the expression like:

IIf(Expression, True Part, False Part)

your code reads:

IIf(Expression, True Part)

In other words, what happens if the expression:

SoldWeb='N'

does not evaluate to 'N'?

You need something like:

TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth),0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Not really sure what that means, can you help me out a little bit more?

Thanks

Ok this is the expression I entered in the query:
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth))
now I'm getting this error message: The expression you entered contains
invalid syntax you may have entered a comma without a proceding value
or
identifier.

Thanks

From memory, IIF requires 3 parts, and your expression shows only 2.
You *must*
provide an expression for the FALSE case.

 

[teelee]

I entered your expression and now I'm getting this error message: The
Expressions you entered contains invalid syntax. You may have entered an
operand without an operator. Any suggestions?
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth),0)

Thanks

Immediate If (IIf) requires an evaluation of the expression like:

IIf(Expression, True Part, False Part)

your code reads:

IIf(Expression, True Part)

In other words, what happens if the expression:

SoldWeb='N'

does not evaluate to 'N'?

You need something like:

TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth),0)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Not really sure what that means, can you help me out a little bit more?

Thanks

Ok this is the expression I entered in the query:
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth))
now I'm getting this error message: The expression you entered contains
invalid syntax you may have entered a comma without a proceding value
or
identifier.

Thanks


From memory, IIF requires 3 parts, and your expression shows only 2.
You *must*
provide an expression for the FALSE case.

 

[Clif McIrvin]

I entered your expression and now I'm getting this error message: The
Expressions you entered contains invalid syntax. You may have entered an
operand without an operator. Any suggestions?  
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth),0)

Thanks

Without having your exact field names it's difficult to answer a
syntax error.

Assuming that all of the fieldnames you are referencing are controls
on your form, it looks like Access might be looking at

[TPM]WebCharge

and seeing two different objects: [TPM] and WebCharge. Without an
operator ( + - * ) or whatever, it doesn't know what to do.

On the other hand, if these fields involve both the form and the
subform you will need to tell Access where to find them.

Good luck.

 

[teelee]

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

I entered your expression and now I'm getting this error message: The
Expressions you entered contains invalid syntax. You may have entered an
operand without an operator. Any suggestions?
TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth),0)

Thanks

Without having your exact field names it's difficult to answer a
syntax error.

Assuming that all of the fieldnames you are referencing are controls
on your form, it looks like Access might be looking at

[TPM]WebCharge

and seeing two different objects: [TPM] and WebCharge. Without an
operator ( + - * ) or whatever, it doesn't know what to do.

On the other hand, if these fields involve both the form and the
subform you will need to tell Access where to find them.

Good luck.

 

[Clif McIrvin]

On my Form I have all controls as Currancy The Field names I have are Costof
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])

 

[teelee]

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

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

 

[John W. Vinson]

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

 

[teelee]

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
Thanks

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

 

[teelee]

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

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

 

[Arvin Meyer [MVP]]

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

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

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

 

[teelee]

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

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

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

On Tue, 8 Apr 2008 17:37:13 -0700, teelee
<[email protected]>
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...

 

[Clif McIrvin]

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

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

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

 

[teelee]

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

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

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

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

 

[John W. Vinson]

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.