Calcs in fields

  • Thread starter Thread starter E
  • Start date Start date
E

E

I am having trouble attempting to figure out how to
perform calculations in fields? Can anyone tell me what
I am doing wrong? I am in the Control source of
properties for this particular field, and this is what I
have for my expression:

( «Expr» = [CMCR Table]![SalePrice] + [CMCR Table]!
[SalesTax] - [CMCR Table]![Discount] - [CMCR Table]!
[Deposit] )

Thanks
 
Try this instead:

= ([CMCR Table]![SalePrice]) + ([CMCR Table]![SalesTax]) -
([CMCR Table]![Discount]) - ([CMCR Table]![Deposit])

Note: the equals sign needs to be there.
 
I am having trouble attempting to figure out how to
perform calculations in fields? Can anyone tell me what
I am doing wrong? I am in the Control source of
properties for this particular field, and this is what I
have for my expression:

( «Expr» = [CMCR Table]![SalePrice] + [CMCR Table]!
[SalesTax] - [CMCR Table]![Discount] - [CMCR Table]!
[Deposit] )

Thanks
Click to expand...

a) You can't do calculations in Fields.
Fields store data, not manipulate data.

b) You can perform calculations in unbound controls in a Form or in a
Report, or in a new column in a Query.

c) You haven't stated what your problem is, other than you're having a
problem, so how can anyone tell you what your problem is.

d) While this is posted in a formscoding newsgroup, you state that you
are trying to do this in a control's control source.

As a guess, try it this way:

= Nz([SalePrice]) + Nz([SalesTax]) - Nz([Discount]) - Nz([Deposit])

The above assumes that all of the above 4 fields are included in your
form's record source, and that [Discount] is a currency amount, i.e.
$5.00, not a percentage, i.e. 15%.. and that possibly, one of the
fields used in the expression might be empty (Null).

Make sure the name of this control is not the same as the name of one
of the fields used in the expression. Use an Unbound control.

The above calculation will appear on your form, however it is not, nor
should it be, actually stored in any Field in a table. Whenever you
need the results of the calculation, re-calculate it.

Now if the above does not work, it's going to be up to you to let us
know what you're doing, when you're doing it, and where, with a short
example of your data, and what your result should be, as well as what
result you're actually getting.
 
Nope, it didn't work? I even tried this to see if it
would work:

=(([CMCR Table]!SalePrice)+([CMCR Table]!SalesTax)-([CMCR
Table]!Discount)-([CMCR Table]!Deposit))

Here is the SalesPrice one: Perhaps this is the problem?

=(([CMCR Table]![Set-up Surcharge])+([CMCR Table]![Audio-
Visual Surcharge])+([CMCR Table]![Hosted Bar PP])+([CMCR
Table]![Cash Bar Hourly])+([CMCR Table]![Drink Tickets
PP])+([CMCR Table]![Hors D'oeuvres PP])+([CMCR Table]!
[Entree 1 PP])+([CMCR Table]![Entree 2 PP])+([CMCR Table]!
[Entree 3 PP])+([CMCR Table]![Desert PP])+([CMCR Table]!
[Wedding Cake Surcharge])+([CMCR Table]![Candlebra
Surcharge])+([CMCR Table]![Champagne Toast Surcharge])+
([CMCR Table]![Tables Surcharge])+([CMCR Table]![Guest
Book Table Surcharge])+([CMCR Table]![Gift Table
Surcharge])+([CMCR Table]![Linens Surcharge])+([CMCR
Table]![Napkins Surcharge])+([CMCR Table]![Centerpieces
Surcharge])+([CMCR Table]![Dance Floor Surcharge])+([CMCR
Table]![Media Show Surcharge])+([CMCR Table]!
[Entertainment Surcharge])+([CMCR Table]![Photographer
Surcharge])+([CMCR Table]![Florist Surcharge])+([CMCR
Table]![Favors, programs, decorations surcharge])+([CMCR
Table]![Service Charges])-([CMCR Table]!Discount))

Any ideas?? ha ha ha .. it is a long one. Thanks for
your help in advance.
-----Original Message-----
Try this instead:

= ([CMCR Table]![SalePrice]) + ([CMCR Table]! [SalesTax]) -
([CMCR Table]![Discount]) - ([CMCR Table]![Deposit])

Note: the equals sign needs to be there.


I am having trouble attempting to figure out how to
perform calculations in fields? Can anyone tell me what
I am doing wrong? I am in the Control source of
properties for this particular field, and this is what I
have for my expression:

( «Expr» = [CMCR Table]![SalePrice] + [CMCR Table]!
[SalesTax] - [CMCR Table]![Discount] - [CMCR Table]!
[Deposit] )

Thanks
Click to expand...

.
Click to expand...
 
Nope, it didn't work? I even tried this to see if it
would work:

=(([CMCR Table]!SalePrice)+([CMCR Table]!SalesTax)-([CMCR
Table]!Discount)-([CMCR Table]!Deposit))

Here is the SalesPrice one: Perhaps this is the problem?

=(([CMCR Table]![Set-up Surcharge])+([CMCR Table]![Audio-
Visual Surcharge])+([CMCR Table]![Hosted Bar PP])+([CMCR
Table]![Cash Bar Hourly])+([CMCR Table]![Drink Tickets
PP])+([CMCR Table]![Hors D'oeuvres PP])+([CMCR Table]!
[Entree 1 PP])+([CMCR Table]![Entree 2 PP])+([CMCR Table]!
[Entree 3 PP])+([CMCR Table]![Desert PP])+([CMCR Table]!
[Wedding Cake Surcharge])+([CMCR Table]![Candlebra
Surcharge])+([CMCR Table]![Champagne Toast Surcharge])+
([CMCR Table]![Tables Surcharge])+([CMCR Table]![Guest
Book Table Surcharge])+([CMCR Table]![Gift Table
Surcharge])+([CMCR Table]![Linens Surcharge])+([CMCR
Table]![Napkins Surcharge])+([CMCR Table]![Centerpieces
Surcharge])+([CMCR Table]![Dance Floor Surcharge])+([CMCR
Table]![Media Show Surcharge])+([CMCR Table]!
[Entertainment Surcharge])+([CMCR Table]![Photographer
Surcharge])+([CMCR Table]![Florist Surcharge])+([CMCR
Table]![Favors, programs, decorations surcharge])+([CMCR
Table]![Service Charges])-([CMCR Table]!Discount))

Any ideas?? ha ha ha .. it is a long one. Thanks for
your help in advance.
-----Original Message-----
Try this instead:

= ([CMCR Table]![SalePrice]) + ([CMCR Table]! [SalesTax]) -
([CMCR Table]![Discount]) - ([CMCR Table]![Deposit])

Note: the equals sign needs to be there.


I am having trouble attempting to figure out how to
perform calculations in fields? Can anyone tell me what
I am doing wrong? I am in the Control source of
properties for this particular field, and this is what I
have for my expression:

( «Expr» = [CMCR Table]![SalePrice] + [CMCR Table]!
[SalesTax] - [CMCR Table]![Discount] - [CMCR Table]!
[Deposit] )

Thanks
Click to expand...

.
Click to expand...
 
I apologize for my ignorance, but if it was not for my
ignorance I would not be asking the questions would I?
You are correct when saying that I should not have used
the word field. When I say fields, I meant the fields in
the form not in a table. (hmmm... although is it not the
same? Don't you use the form "field" to enter data into
your table "field"?) Never mind. I will show you exactly
what I am looking for.

Form:

Surcharge1 = $5.00
Surcharge2 = $1.00
Surcharge3 = $2.00

SalesPrice = $8.00 (This is the sum calc that I need)
Tax = $0.48
Discount = $2.00 (SalesPrice * .06 or whatever)

Deposit = $5.00

TotalAmount = $1.48 (=SalesPrice+Tax-Discount-Discount)

The Total Amount is the one that I posted... See the post
I just posted to see the SalesPrice formula.

These are entered in the CONTROL SOURCE under ALL in the
properties tab for the "data entry field." Not sure if I
can explain it better. Sorry... What does the nz stand
for?????? Thanks for your help in advance.
 
E said:
I apologize for my ignorance, but if it was not for my
ignorance I would not be asking the questions would I?
You are correct when saying that I should not have used
the word field. When I say fields, I meant the fields in
the form not in a table. (hmmm... although is it not the
same? Don't you use the form "field" to enter data into
your table "field"?)
Click to expand...

Actually, you have controls on forms, not fields.
Sorry... What does the nz stand
for?????? Thanks for your help in advance.
Click to expand...

The Nz function will substitute a value for a Null (and I'd go one step
further than Fred suggested, and try

= Nz([SalePrice], 0) + Nz([SalesTax], 0) - Nz([Discount], 0) - Nz([Deposit],
0)

to be positive that you get a 0 if there's no value.
 
You did it!!! Thank you so much, and I promise that I
will never ever ever ever call a CONTROL a damn field
ever again!!! ha ha ha ... If you don't know the proper
name you call it what you want until you are informed of
what it really is!!! NOW I KNOW... THANKS AGAIN FOR ALL
YOUR HELP!!!!
-----Original Message-----
I apologize for my ignorance, but if it was not for my
ignorance I would not be asking the questions would I?
You are correct when saying that I should not have used
the word field. When I say fields, I meant the fields in
the form not in a table. (hmmm... although is it not the
same? Don't you use the form "field" to enter data into
your table "field"?)
Click to expand...

Actually, you have controls on forms, not fields.
Sorry... What does the nz stand
for?????? Thanks for your help in advance.
Click to expand...

The Nz function will substitute a value for a Null (and I'd go one step
further than Fred suggested, and try

= Nz([SalePrice], 0) + Nz([SalesTax], 0) - Nz
Click to expand...
([Discount], 0) - Nz([Deposit],
 
Back
Top