Calculations

[Patty S]

I am stumped about how to write an expression.

fields 1, 2 & 3 are summed in field 4. field 5 is a calculated field to be
subtracted from field 4. Field 5 can only bebe equal to or less than field 4.

Please help.

 

[Jeff Boyce]

Patty

Where are you trying to do this?

Is this an MS Access question?

Those kinds of calculations and comparisons work well in a tool designed to
do them ... i.e., Excel.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Patty S]

I understand that this would be better in a different application, but I am
forced to use MS Access 2003. Any ideas?

 

[John Spencer]

Are you trying to do this in a query, on a form, in a report, in a VBA
function? Replace field5Expression with the calculation you are using to
generate the value in field5.

IIF(field5Expression<Nz(Field1,0) + Nz(Field2,0) + Nz(Field3,0),
Nz(Field1,0) + Nz(Field2,0) + Nz(Field3,0)-Field5Expression,0)

I'm not sure what you want to do if field5 is MORE than the sum of fields 1,
2, and 3. I made the assumption (guess) that you would want to return zero.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve]

Create a query based on the table that contains Fields 1 to 3 and 5. Put the
following expression in an empty field in the query:

Field4 = [NameOfField1] + [NameOfField2] + [NameOfField3]

Put the following in a different blank field in the query:
Field4MinusField5 = Field4 - [NameOfField5]
Put the following expression in the criteria of this field:

=0

The above criteria assures the query will only return records where Field 5
is equal to or less than field 4..


It is not clear from your problem description what Field5 is. In the above,
I assumed it is a field in the same table as Fields 1 to 3.

Steve
(e-mail address removed)