Sum IIF Expression One Field Two Codes

[Paperback Writer]

I want to sum two values within the same field, but with different codes.

IIf([Rollup]="Expiration", .... then I want it to sum where code equals 4 or
5.

"Code" can be 1 of several different values. In this case, I'm only
interested in codes 4 and 5.

I know what is below is in bad form, I'm just parsing it in the way that
would seem to make sense with the IIF statement.

IIf([CR Rollup]="Expiration", .... [Code = 4.Expiration]+[Code = 5.
Expiration],0)

Thank you!!!

 

[John W. Vinson]

I want to sum two values within the same field, but with different codes.

IIf([Rollup]="Expiration", .... then I want it to sum where code equals 4 or
5.

"Code" can be 1 of several different values. In this case, I'm only
interested in codes 4 and 5.

I know what is below is in bad form, I'm just parsing it in the way that
would seem to make sense with the IIF statement.

IIf([CR Rollup]="Expiration", .... [Code = 4.Expiration]+[Code = 5.
Expiration],0)

Thank you!!!

What is it that you want to sum? Is there a number field in your table -
Exipiration, or something else?

The syntax would be

IIF([CR Rollup] = "Expiration" AND (

 = 4 OR [Code] = 5), [Expiration],
0)

Replace [Expiration] with the name of the field that you want summed if I'm
misunderstanding.

 

[Paperback Writer]

Here's an example table. In this example, the sum would be 450.

Code CR Rollup Value
1 Reserve 50
2 Reserve 100
3 Expiration 150
4 Expiration 200
5 Expiration 250

I need to use IIF to sum values related to codes 4 & 5, with a CR Rollup of
"Expiration".

Thank you!!!

I want to sum two values within the same field, but with different codes.

IIf([Rollup]="Expiration", .... then I want it to sum where code equals 4 or
5.

"Code" can be 1 of several different values. In this case, I'm only
interested in codes 4 and 5.

I know what is below is in bad form, I'm just parsing it in the way that
would seem to make sense with the IIF statement.

IIf([CR Rollup]="Expiration", .... [Code = 4.Expiration]+[Code = 5.
Expiration],0)

Thank you!!!

What is it that you want to sum? Is there a number field in your table -
Exipiration, or something else?

The syntax would be

IIF([CR Rollup] = "Expiration" AND (

 = 4 OR [Code] = 5), [Expiration],
0)

Replace [Expiration] with the name of the field that you want summed if I'm
misunderstanding.[/QUOTE]

 

[Paperback Writer]

Anyone?

Help?

Please...

Here's an example table. In this example, the sum would be 450.

Code CR Rollup Value
1 Reserve 50
2 Reserve 100
3 Expiration 150
4 Expiration 200
5 Expiration 250

I need to use IIF to sum values related to codes 4 & 5, with a CR Rollup of
"Expiration".

Thank you!!!

I want to sum two values within the same field, but with different codes.

IIf([Rollup]="Expiration", .... then I want it to sum where code equals 4 or
5.

"Code" can be 1 of several different values. In this case, I'm only
interested in codes 4 and 5.

I know what is below is in bad form, I'm just parsing it in the way that
would seem to make sense with the IIF statement.

IIf([CR Rollup]="Expiration", .... [Code = 4.Expiration]+[Code = 5.
Expiration],0)

Thank you!!!

What is it that you want to sum? Is there a number field in your table -
Exipiration, or something else?

The syntax would be

IIF([CR Rollup] = "Expiration" AND (

 = 4 OR [Code] = 5), [Expiration],
0)

Replace [Expiration] with the name of the field that you want summed if I'm
misunderstanding.[/QUOTE][/QUOTE]

 

[John W. Vinson]

Here's an example table. In this example, the sum would be 450.

Code CR Rollup Value
1 Reserve 50
2 Reserve 100
3 Expiration 150
4 Expiration 200
5 Expiration 250

I need to use IIF to sum values related to codes 4 & 5, with a CR Rollup of
"Expiration".

Thank you!!!

In that case sum:

IIF([CR Rollup] = "Expiration" AND (

 = 4 OR [Code] = 5), [Value],0)

 

[Paperback Writer]

The combining must be done on the [value] part.

The first part needs to just search on the CR Rollup, the second part needs
to sum codes 4 and 5.

(1) IIF([CR Rollup] = "Expiration",

Then

(2) [Value] must be summed for codes 4 & 5.

Full disclosure - This is part of a much larger piece of logic, but this
part is the last thing I need to do.

Thanks again!!!

Here's an example table. In this example, the sum would be 450.

Code CR Rollup Value
1 Reserve 50
2 Reserve 100
3 Expiration 150
4 Expiration 200
5 Expiration 250

I need to use IIF to sum values related to codes 4 & 5, with a CR Rollup of
"Expiration".

Thank you!!!

In that case sum:

IIF([CR Rollup] = "Expiration" AND (

 = 4 OR [Code] = 5), [Value],0)[/QUOTE]

 

[John W. Vinson]

The combining must be done on the [value] part.

The first part needs to just search on the CR Rollup, the second part needs
to sum codes 4 and 5.

(1) IIF([CR Rollup] = "Expiration",

Then

(2) [Value] must be summed for codes 4 & 5.

Full disclosure - This is part of a much larger piece of logic, but this
part is the last thing I need to do.

Thanks again!!!

Thank you for the context. If you would care to post it in a comprehensible
manner someone might be able to help.

My only guess is that you should use a criterion of ="Expiration" on the CR
Rollup field, in the query of which this is a part. But I can only see what
you've posted, and it's hard to figure out what you're trying to accomplish.

Note also that everyone who posts here is an unpaid volunteer. Posting a
"help, anyone?" post after four hours is just plain rude.

 

[Paperback Writer]

I think if you look from the very first post, I was very explicit in stating
that the second half of the statement needed to be summed -- Not the first
half. I'm not sure what syntax to use when I need to sum two fields in the
true value portion of the statement.

If you don't know how to do it, just say so. I hope someone else familiar
enough with this can be of assistance.

If you can point out where my request for "help" is in conflict with user
group rules, then I'll take it as point taken. Otherwise, I think I'm in
line with what everyone else does. I think it would be more rude start a
second thread, which is quite common.

The combining must be done on the [value] part.

The first part needs to just search on the CR Rollup, the second part needs
to sum codes 4 and 5.

(1) IIF([CR Rollup] = "Expiration",

Then

(2) [Value] must be summed for codes 4 & 5.

Full disclosure - This is part of a much larger piece of logic, but this
part is the last thing I need to do.

Thanks again!!!

Thank you for the context. If you would care to post it in a comprehensible
manner someone might be able to help.

My only guess is that you should use a criterion of ="Expiration" on the CR
Rollup field, in the query of which this is a part. But I can only see what
you've posted, and it's hard to figure out what you're trying to accomplish.

Note also that everyone who posts here is an unpaid volunteer. Posting a
"help, anyone?" post after four hours is just plain rude.

 

[John Spencer]

PERHAPS you are looking for something like the following.

SELECT IIF(Code in (4,5) AND [CR Rollup]="Expiration","E45","E" & Code)
As CodeGroup,
Sum([Value]) as Total
FROM [Your Table]
GROUP BY IIF(Code in (4,5) AND [CR Rollup]="Expiration","E45","E"&Code)

I say perhaps because I find it difficult to discern exactly what you
want. Ninety percent of the solution is understanding the problem and
unfortunately understanding your problem seems to be difficult.


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

I think if you look from the very first post, I was very explicit in stating
that the second half of the statement needed to be summed -- Not the first
half. I'm not sure what syntax to use when I need to sum two fields in the
true value portion of the statement.

If you don't know how to do it, just say so. I hope someone else familiar
enough with this can be of assistance.

If you can point out where my request for "help" is in conflict with user
group rules, then I'll take it as point taken. Otherwise, I think I'm in
line with what everyone else does. I think it would be more rude start a
second thread, which is quite common.

The combining must be done on the [value] part.

The first part needs to just search on the CR Rollup, the second part needs
to sum codes 4 and 5.

(1) IIF([CR Rollup] = "Expiration",

Then

(2) [Value] must be summed for codes 4 & 5.

Full disclosure - This is part of a much larger piece of logic, but this
part is the last thing I need to do.

Thanks again!!!

Thank you for the context. If you would care to post it in a comprehensible
manner someone might be able to help.

My only guess is that you should use a criterion of ="Expiration" on the CR
Rollup field, in the query of which this is a part. But I can only see what
you've posted, and it's hard to figure out what you're trying to accomplish.

Note also that everyone who posts here is an unpaid volunteer. Posting a
"help, anyone?" post after four hours is just plain rude.