Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Forms / April 2008

Tip: Looking for answers? Try searching our database.

Please Help - Need to finish this project

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
teelee - 07 Apr 2008 00:59 GMT
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
Reply to this Message
Arvin Meyer [MVP] - 07 Apr 2008 01:36 GMT
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]
Signature

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
[quoted text clipped - 18 lines]
>
> Thanks
Reply to this Message
teelee - 08 Apr 2008 00:41 GMT
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:
>
[quoted text clipped - 25 lines]
> >
> > Thanks
Reply to this Message
Clif McIrvin - 08 Apr 2008 01:03 GMT
> Ok this is the expression I entered in the query:
> TotalRevenue:IIF(SoldWeb='N',([Cost]-[Eplan]-[TPM]WebCharge-TPM-Auth))
[quoted text clipped - 3 lines]
>
> Thanks

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

--
Clif

Fellow Access learner
Reply to this Message
teelee - 08 Apr 2008 01:28 GMT
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))
[quoted text clipped - 12 lines]
>
> Fellow Access learner
Reply to this Message
Arvin Meyer [MVP] - 08 Apr 2008 13:22 GMT
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)
Signature

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?
>
[quoted text clipped - 17 lines]
>>
>> Fellow Access learner
Reply to this Message
teelee - 08 Apr 2008 22:31 GMT
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:
>
[quoted text clipped - 34 lines]
> >>
> >> Fellow Access learner
Reply to this Message
Clif McIrvin - 08 Apr 2008 23:28 GMT
> 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
Reply to this Message
teelee - 08 Apr 2008 23:55 GMT
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
[quoted text clipped - 21 lines]
> --
> Clif
Reply to this Message
Clif McIrvin - 09 Apr 2008 00:31 GMT
> 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
[quoted text clipped - 6 lines]
>
> 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
Reply to this Message
teelee - 09 Apr 2008 01:37 GMT
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
[quoted text clipped - 26 lines]
> --
> Clif
Reply to this Message
John W. Vinson - 09 Apr 2008 01:58 GMT
>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...
Signature


            John W. Vinson [MVP]

Reply to this Message
teelee - 09 Apr 2008 02:16 GMT
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

> >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
[quoted text clipped - 3 lines]
> of that name. Are you certain of the spelling? [Sold Web] and [SoldWeb] are
> two different names...
Reply to this Message
teelee - 09 Apr 2008 02:50 GMT
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
[quoted text clipped - 3 lines]
> of that name. Are you certain of the spelling? [Sold Web] and [SoldWeb] are
> two different names...
Reply to this Message
Arvin Meyer [MVP] - 09 Apr 2008 04:15 GMT
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.
Signature

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])
[quoted text clipped - 17 lines]
>> are
>> two different names...
Reply to this Message
teelee - 09 Apr 2008 14:36 GMT
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:
>
[quoted text clipped - 41 lines]
> >> are
> >> two different names...
Reply to this Message
Clif McIrvin - 09 Apr 2008 18:08 GMT
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.
[quoted text clipped - 42 lines]
> >http://www.mvps.org/access
> >http://www.accessmvp.com
Reply to this Message
teelee - 10 Apr 2008 01:37 GMT
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
[quoted text clipped - 57 lines]
> > >http://www.mvps.org/access
> > >http://www.accessmvp.com
Reply to this Message
John W. Vinson - 10 Apr 2008 01:48 GMT
>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.
Signature


            John W. Vinson [MVP]

Reply to this Message
teelee - 10 Apr 2008 13:40 GMT
Ok my field names are as followed: PlanOrderID, Plan Name, Payment Method,
Customer Name, Date Plan Sold, Cost Of Plans, Eplan Charge, Web Charge,
Direct Bill, TPM Charge, AuthNEtCharge, and Total Revenue
"John W. Vinson" wrote: Now what I want to happen is in the form you would
enter the cost of plans, the payment method charge which would be WebCharge,
EPaln Charge, or Direct Bill. Then I would like this to total in the field
name Total Revenue in my form.
The expressions I've been using Total:
IIf([TotalRevenue]='N',[CostofPlans],[WebCharge]) When I enter this
expression I get only the WebCharge

With this expression: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 only
Total:
IIf([TotalRevenue]='N',([CostofPlans]-[EPlanCharge]),([WebCharge]-[TPMCharge]))

So you see what's happening: I need it to total what information that I
enter for each Plan that is sold. If a Plan was sold by EPlan then I need it
to total this way: CostofPlan minus the WebCharge minus the TPM Charge minus
the AuthNetCharge.

And if the plan was purchased by EPlan Charge I need the total to say
WebCharge minus TPM Charge minus AuthNetCharge.

And if was purchased by Direct Bill then in the total field that's what
would be there. What I had entered into the form as the amount of the direct
bill. Any help is very much appreciated, I'm at the last point on my project
and I really need to finish it soon.

Thanks in advance

> >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
[quoted text clipped - 12 lines]
> expression you used, what result you wanted, and what result you got) that
> someone could help.
Reply to this Message
Clif McIrvin - 10 Apr 2008 14:26 GMT
> 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, what I was asking you to do is to copy and paste YOUR
expressions into your posts here so that we can see exactly what you
are working with, not the other way around.

See John Vinson's excellent post this morning for more explanation.

--
Clif
Reply to this Message
teelee - 08 Apr 2008 01:34 GMT
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))
[quoted text clipped - 12 lines]
>
> Fellow Access learner
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.