Solved: Combining a SUM and IIF statement

[David627]

APP: Access 2007

I am looking to nest a SUM statement in an IIF statement - the intent - only if a particular condition is met, SUM those values.

I have a PERMIT field which has different values. I have a report in which I have these fields in the report footer so it is cumulative totals. The report will pull in dates specified by the user through a parameter query.

Fields are:

TOTAL PERMIT 1 issued
TOTAL PERMIT 2 issued
TOTAL PERMIT 4 issued
TOTAL PERMIT 17 issued
TOTAL PERMIT 30 issued
etc...

So I want the text box in the Total Permit 1 field to see if the fields are 1 and if so sum them. Same for the other fields of which they are all of different values (not just 1 or 0, but 4, 17, 30, etc.).

Does the SUM function come first or does the IIF? Syntax examples would be very helpful...I am concerned with syntax as Access is unforgivable if it is not correct, especially with parenthesis.

Thanks
David

[OBP]

The iif should come first, however it is going to be quite a few nests.
I would be more inclined to do this in a Query, based on the current parameter query, but use the Permit field to Group by and use Sum for the fields that you wish to sum.
You can then place a Subreport based on that query in the Report's footer.

[David627]

The iif should come first, however it is going to be quite a few nests.
I would be more inclined to do this in a Query, based on the current parameter query, but use the Permit field to Group by and use Sum for the fields that you wish to sum.
You can then place a Subreport based on that query in the Report's footer.
Thanks OBP for your response...

Do I have to nest that much?

Couldn't it look like this?...

PERMIT 1 text box would say IIF(Permit=1(SUM([Permitvaluesfound])))
PERMIT 2 text box would say IIF(Permit=2(SUM([Permitvaluesfound])))
PERMIT 4 text box would say IIF(Permit=4(SUM([Permitvaluesfound])))
PERMIT 17 text box would say IIF(Permit=17(SUM([Permitvaluesfound])))
PERMIT 30 text box would say IIF(Permit=30(SUM([Permitvaluesfound])))
etc.

Is what I proposed above more complicated than the query option??

Thanks again.
David

[OBP]

I am not sure that you can do that in the Report footer, but you can try it.
the correct syntax would be

=IIF(Permit=1,(SUM([Permitvaluesfound])))

The problem could be that it can't differentiate the value of Permit in the individual records.

You could have Unbound fields on the Details section that have
=IIF(Permit=1,[Permitvaluesfound]) and then set their Running Sum Properties to Yes overall.
And then have you Footer Unbound Fields pick up the value of the those fields. It would only show the last (Sum) value for each field.

[slurpee55]

I tried that syntax OBP and got that in a report the sum of all the values found (not only those which had a permit value=1) - but it only displayed the sum where the value was =1. :wot

[David627]

Technically I need to count what I find and not sum....

I tried the following with #Error message results

=IIF([permit type]=1,(COUNT([permit TYPE])))
=IIF([permit type]=4,(COUNT([permit TYPE])))
=IIF([permit type]=10,(COUNT([permit TYPE])))
=IIF([permit type]=11,(COUNT([permit TYPE])))
=IIF([permit type]=14,(COUNT([permit TYPE])))

[CreganTur]

This is just off the top of my head, but part of the problem is probably the fact that you did not provide anything for the False parameter of the IIF. Try(broken over multiple lines for post):
=IIF([permit type]=1,(COUNT([permit TYPE])), ([permit type]=4,(COUNT([permit TYPE]))
, ([permit type]=10,(COUNT([permit TYPE])), ([permit type]=11,(COUNT([permit TYPE]))
, ([permit type]=14,(COUNT([permit TYPE])), "")))))

My syntax may be a little off... nesting multiple IIF's with their parentheses can get confusing.

[David627]

I'm thinking a DCount would work - what are your thoughts? I tried it and get an #Error. My syntax us:

=DCount("[PAYER]","RPT SUM Query","[permit type] = '1'")

I am just looking to count the amount of records so it really doesn't matter what field I pick. I also tried it as =DCount("[PAYER]","RPT SUM Query","[permit type] = 1") and I've received the same error.

Thanks
David

[CreganTur]

Hmm...:think: Try this:

=DCount("PAYER","[RPT SUM Query]","[permit type] = 1")

I think the issue is that brackets should be used to wrap table/field names that have spaces in them. You don't need them around PAYER, but you do need them around the other two items. See if that works.

Also, what is the data type of the field permit type? If it is a number data type, then you do not need to wrap the 1 in symbols. But, if it is a string, then you will need to wrap it in single quotes: '1'

[David627]

Permit type is number. I used your suggestion - same error.... :banghead:
Working on this for a long time!!! Argghh!:igiveup:

[David627]

I even changed the name of the query so it doesn't have spaces and put it in =DCount("PAYER","rptSUMQuery","[permit type] = 1") and =DCount("PAYER","rptSUMQuery","[permit type] = '1'")

[David627]

Well it works - after reading the documentation it says "...you can include the name of a field in a table, a control on a form, a constant, or a function..." There is nothing about using a query!! When I changed it to the table it worked like a charm!! Thanks to all.


One BIG hitch... I will need to extract specific dates and if my report is based on a table verus a paramenter query how would I do this????

David

[CreganTur]

One BIG hitch... I will need to extract specific dates and if my report is based on a table verus a paramenter query how would I do this????


You could try a filter...:dunno

[David627]

Randy:

What would be the syntax that would allow the user to input (via a prompt like a parameter query) a date range?

Thanks

David

[CreganTur]

What would be the syntax that would allow the user to input (via a prompt like a parameter query) a date range?


I'm really not sure what you mean by this, since in a post above you state that you're not going to be able to use a parameter query.

Are you talking about wanting to give the user a way to set the filter, in a way that is similar to a parameter query?

[David627]

Are you talking about wanting to give the user a way to set the filter, in a way that is similar to a parameter query?

Yes. Is there a way to use the filter option (in report properties, Data tab, filter) to enable the user to enter a date range?

I've seen the following VBA example:


strFilter = "[strState] = '" & Me!lstState & "'"
DoCmd.OpenReport "rptCustomer", acViewPreview, , strFilter

Can something similar be used for dates where the code would prompt the user in a msgbox?

David

[CreganTur]

Just use an input box to capture the date the user wan'ts to view. Run it through some validation to ensure that it's actually a date, and then plug it into the filter. Try the On Open event for the report.

[David627]

Just use an input box to capture the date the user wan'ts to view. Run it through some validation to ensure that it's actually a date, and then plug it into the filter. Try the On Open event for the report.



Could you give me sample code? Would be much appreciated!

David

[David627]

please see if I am on the correct track - thanks


Private Sub Report_Open(Cancel As Integer)

Dim dtmBeginDate As Date
Dim dtmEndDate As Date

'prompt user for beginning date
dtmBeginDate = InputBox("Please enter report Begin date:")

'validate date
If IsDate(dtmBeginDate) Then

'prompt user for ending date
dtmEndDate = InputBox("Please enter report End date:")

Else

'invalid date message
MsgBox "Please enter a valid date", vbCritical
dtmBeginDate = InputBox("Please enter report Begin date:")

End If

End Sub

[CreganTur]

That looks good for getting the user's responses. Now you just need to plug the gathered dated into your report's filter. Check Access help for more information on the filter method.

[David627]

did this via a macro that calls a delete query and a make table query with the parameter dates.

Thanks to all!