Conditional sum

[Villagio]

I selected ChemCode,VaporPressure, percentageOfIngredient fields on the
report from the table. I need 2 text box that display Vapor Pressure value
and Vapor Pressure total that >= 0.1.

The first text box, I don't have problem to create it, but the 2nd one, I
just could not get the total value. I tried =sum([VaporPressure]>=0.1), it
did not work. I also tried = sum(iif([VaporPressure]>=0.1,1,0)) , it gave me
a value that is bigger than total value of VaporPressure field.

Am I missing something? Please help. Thanks.

Peter

 

[Duane Hookom]

Try:
=Sum(Abs([VaporPressure]>=0.1) * [VaporPressure])

 

[Villagio]

Great, it works, I got the exact value that I needed. But I don't
understand why you have to multiply the VaporPressure value itself?

Thanks. Duane.

Try:
=Sum(Abs([VaporPressure]>=0.1) * [VaporPressure])

--
Duane Hookom
Microsoft Access MVP

I selected ChemCode,VaporPressure, percentageOfIngredient fields on the
report from the table. I need 2 text box that display Vapor Pressure value
and Vapor Pressure total that >= 0.1.

The first text box, I don't have problem to create it, but the 2nd one, I
just could not get the total value. I tried =sum([VaporPressure]>=0.1), it
did not work. I also tried = sum(iif([VaporPressure]>=0.1,1,0)) , it gave me
a value that is bigger than total value of VaporPressure field.

Am I missing something? Please help. Thanks.

Peter

 

[Duane Hookom]

I am multiplying the VaporPressure by either 0 or 1. The following expression
creates the multiplier:

Abs([VaporPressure]>=0.1)
--
Duane Hookom
Microsoft Access MVP

Great, it works, I got the exact value that I needed. But I don't
understand why you have to multiply the VaporPressure value itself?

Thanks. Duane.

Try:
=Sum(Abs([VaporPressure]>=0.1) * [VaporPressure])

--
Duane Hookom
Microsoft Access MVP

I selected ChemCode,VaporPressure, percentageOfIngredient fields on the
report from the table. I need 2 text box that display Vapor Pressure value
and Vapor Pressure total that >= 0.1.

The first text box, I don't have problem to create it, but the 2nd one, I
just could not get the total value. I tried =sum([VaporPressure]>=0.1), it
did not work. I also tried = sum(iif([VaporPressure]>=0.1,1,0)) , it gave me
a value that is bigger than total value of VaporPressure field.

Am I missing something? Please help. Thanks.

Peter

 

[Villagio]

Duane,

Now I got it. Thanks again.

Peter

I am multiplying the VaporPressure by either 0 or 1. The following expression
creates the multiplier:

Abs([VaporPressure]>=0.1)
--
Duane Hookom
Microsoft Access MVP

Great, it works, I got the exact value that I needed. But I don't
understand why you have to multiply the VaporPressure value itself?

Thanks. Duane.

Try:
=Sum(Abs([VaporPressure]>=0.1) * [VaporPressure])

--
Duane Hookom
Microsoft Access MVP


:

I selected ChemCode,VaporPressure, percentageOfIngredient fields on the
report from the table. I need 2 text box that display Vapor Pressure value
and Vapor Pressure total that >= 0.1.

The first text box, I don't have problem to create it, but the 2nd one, I
just could not get the total value. I tried =sum([VaporPressure]>=0.1), it
did not work. I also tried = sum(iif([VaporPressure]>=0.1,1,0)) , it gave me
a value that is bigger than total value of VaporPressure field.

Am I missing something? Please help. Thanks.

Peter

 

[Villagio]

Duane,

I totally mis-understood our lab's request. What they wanted was the
total of the Percentage that have the VaporPressure >= 0.1

In English, it will be something like IF VAPORPRESSURE >= 0.1, THEN SUM
ALL THOSE VALUE IN PERCENTAGE FIELD.

What is the correct syntax to put it in text box on the report? Thanks.

Peter

Try:
=Sum(Abs([VaporPressure]>=0.1) * [VaporPressure])

--
Duane Hookom
Microsoft Access MVP

I selected ChemCode,VaporPressure, percentageOfIngredient fields on the
report from the table. I need 2 text box that display Vapor Pressure value
and Vapor Pressure total that >= 0.1.

The first text box, I don't have problem to create it, but the 2nd one, I
just could not get the total value. I tried =sum([VaporPressure]>=0.1), it
did not work. I also tried = sum(iif([VaporPressure]>=0.1,1,0)) , it gave me
a value that is bigger than total value of VaporPressure field.

Am I missing something? Please help. Thanks.

Peter

 

[John Spencer]

Summing percentages does not seem to make a lot of sense, however,

=Sum(IIF([VaporPressure]>=0.1,[PercentageField],Null))

Or use this variant if Duane's solution.

=Sum(Abs([VaporPressure]>=0.1) * [PercentageField])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Duane,

I totally mis-understood our lab's request. What they wanted was the
total of the Percentage that have the VaporPressure >= 0.1

In English, it will be something like IF VAPORPRESSURE >= 0.1, THEN SUM
ALL THOSE VALUE IN PERCENTAGE FIELD.

What is the correct syntax to put it in text box on the report? Thanks.

Peter

Try:
=Sum(Abs([VaporPressure]>=0.1) * [VaporPressure])

--
Duane Hookom
Microsoft Access MVP

I selected ChemCode,VaporPressure, percentageOfIngredient fields on the
report from the table. I need 2 text box that display Vapor Pressure value
and Vapor Pressure total that >= 0.1.

The first text box, I don't have problem to create it, but the 2nd one, I
just could not get the total value. I tried =sum([VaporPressure]>=0.1), it
did not work. I also tried = sum(iif([VaporPressure]>=0.1,1,0)) , it gave me
a value that is bigger than total value of VaporPressure field.

Am I missing something? Please help. Thanks.

Peter

 

[Villagio]

John,

=Sum(Abs([VaporPressure]>=0.1) * [PercentageField]) works.

Basically our lab need to know the VOC value by adding the
PercentageField, which is number that will add up to 100 within the formula.
VOC is the total Percentage (or total parts) in a specific formula that have
VaporPressure field that is equal and over 0.1.

Thank you for your help.

Peter

Summing percentages does not seem to make a lot of sense, however,

=Sum(IIF([VaporPressure]>=0.1,[PercentageField],Null))

Or use this variant if Duane's solution.

=Sum(Abs([VaporPressure]>=0.1) * [PercentageField])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Duane,

I totally mis-understood our lab's request. What they wanted was the
total of the Percentage that have the VaporPressure >= 0.1

In English, it will be something like IF VAPORPRESSURE >= 0.1, THEN SUM
ALL THOSE VALUE IN PERCENTAGE FIELD.

What is the correct syntax to put it in text box on the report? Thanks.

Peter

Try:
=Sum(Abs([VaporPressure]>=0.1) * [VaporPressure])

--
Duane Hookom
Microsoft Access MVP


:

I selected ChemCode,VaporPressure, percentageOfIngredient fields on the
report from the table. I need 2 text box that display Vapor Pressure value
and Vapor Pressure total that >= 0.1.

The first text box, I don't have problem to create it, but the 2nd one, I
just could not get the total value. I tried =sum([VaporPressure]>=0.1), it
did not work. I also tried = sum(iif([VaporPressure]>=0.1,1,0)) , it gave me
a value that is bigger than total value of VaporPressure field.

Am I missing something? Please help. Thanks.

Peter