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.

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" <Patty (E-Mail Removed)> wrote in message
news:B552A1F7-760A-4DB0-A0EA-(E-Mail Removed)...
>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.

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

"Jeff Boyce" wrote:

> 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" <Patty (E-Mail Removed)> wrote in message
> news:B552A1F7-760A-4DB0-A0EA-(E-Mail Removed)...
> >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.
>
>
> .
>

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

Patty S wrote:
> 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.

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



"Patty S" <(E-Mail Removed)> wrote in message
news:180AAFF9-B529-4419-A30A-(E-Mail Removed)...
>I understand that this would be better in a different application, but I am
> forced to use MS Access 2003. Any ideas?
>
> "Jeff Boyce" wrote:
>
>> 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" <Patty (E-Mail Removed)> wrote in message
>> news:B552A1F7-760A-4DB0-A0EA-(E-Mail Removed)...
>> >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.
>>
>>
>> .
>>