IIf Statement with a sum (1 Viewer)

[baum12]

I think I need an If statement.

If Grant Status = "Funded" OR "Completed" then I want it to give me a total count of records.

Then I need a separate total for Pending count of records.

I was trying something like this - but this is over my head!

= IIf ( [Grant Status] , "Funded", =Sum([CountOfGrant Status]))

Any help would be appreciated!! Thank you!

 

[Gregof1976]

Im not sure if I well understand your request but lets try this way:

=Sum(IIf([Grant Status] = True Or ([Grant Status] = True); [CountOfGrant Status]; 0))

Regards,
GS

 

[baum12]

Thank you, this helped a bunch!!! Now I need to know how to do the statement for just the pending. I thought I would be able to just delete this part of it

Or ([Grant Status] = True)

but access is giving me an error. So, I cheated and just threw a word in that isn't in the database - but I don't want this to be done like that. If you can help, that would be great.

Also, is there a quick explanation or somewhere I can read on the internet about the parts of this statement. I understand everything except the ;0)) .

Thanks!
Lori

 

[Gregof1976]

Sorry statement should be like this:

=Sum(IIf([Grant Status] = 'Funded' Or ([Grant Status] = 'Completed'); [CountOfGrant Status]; 0))

If you would like only for the one condition then should be like this.

=Sum(IIf([Grant Status] = 'Funded'; [CountOfGrant Status]; 0))


; 0)) this staement means that if the condition is false then return zero... you can leave this as a empty...

I hope that it will help

Reagrds
GS

 

[baum12]

I have quite the mess here! The other things I have asked about work. My report looked wonderful until I added a 2nd record into it! I have attached a pdf with screenshots - would you mind taking a look? Any recommendations would be greatly appreciated. Hope it makes sense. I essentially have 4 reports that need to be in 1 report.

Thank you!
Lori

 

[Beetle]

Most of this appears to be summary information, so you should be able to calculate most of it using a Totals query using Count and/or Sum, then base the report on that query. For the list of Accepted and Published papers you would still need sub-reports or a concatenation function.

If you want to provide some more detailed information on your structure, or attach a copy of your db, someone may be able to offer more detailed advice. If you're using A2007 or later and you're going to attach a copy of your db, save it in .mdb format first

 

[Aryzona]

I have a similar problem. I have a field [papep] that consists of a "type" PA .. or PE then I have a field [papedp] that has a numeric value for documents processed. I need to do a total of documents processed that are PA and a separate total for documents processed that are PE.

I have tried the following:
=Sum(Iif([papep]="PA",[papedp],0))

when I try to run the report, after entering the date range, it tells me the expression is too complex and to try simplifying or using variables.

Why?

The report breaks them out individually line by line for each person
7/01/21 PE 53
7/01/21 PA 12

and the grand total of 65 ... but will not total the PA separately from the PE?

 

[CJ_London]

Usual reason for the expression to complex error is because you have nulls in your data

edit: taken out the last bit, just realised it is two different variables

edit 2 - just realised this is a 10 year old thread @Aryzona you should start a new thread

another method to try

=Sum([papedp] * - ([papep]="PA"))

 

[Aryzona]

=Sum([papedp] * - ([papep]="PA"))

Woot Thank you ... this last one did the trick! I did realize that the DB was storing the list id rather than the PA PE description, so when i swapped a 1 for PA and a 2 for PE it worked like a charm!

=Sum([papedp]*-([papep]=2))

Thank you!!!!!