Total value based on a field value & between dates in report text box 1

[monkeysee]

I have a report that will show 'quarterly' numbers for different types of activities.

Example: I need to calculate how many Septic permits were issued that were NEW on a quarterly basis.

The fields in my underlying query are:
Permit Purpose (which will have the value of New or Repair)
IssueDate (which is the date the permit was issued)

First, I used: =Count(IIf([PermitPurpose],"New"))
it returns a count, but not an accurate one (it also counted those that had the value of "Repair")
Then I changed it to: =Count(IIf([PermitPurpose]="New")) and I now get an error: The function you entered has a function containing the wrong number of arguements

What am I doing wrong?

Second part of my questions, I want it to calculate for all text box values between certain dates (quarterly) and if possible, this being a choice of the user so the same report can be used throughout the year. Is this at all possible?

I am not 'experienced' in vb, so any help will be greatly appreciated.

 

[dhookom]

To perform a conditional count, you simply Sum() the absolute value of the condition. For instance to count the number of records where [Gender] = "f"
=Sum(Abs([Gender] = "f"))
or in your case
=Sum(Abs([PermitPurpose]="New"))
You can combine conditions using " And "
=Sum(Abs(PermitPurpose = "New" And Format([IssueDate],"yyyyq") = Format(Date(),"yyyyq")))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[monkeysee]

Duane, thank you for your help, I tried what you suggested
=Sum(Abs(PermitPurpose = "New" And Format([IssueDate],"yyyyq") = Format(Date(),"yyyyq")))

and am getting the following error:
Syntax error (comma) in query expression.

I experimented and tried removing the first comma and got this error message:
Syntax error (missing operator) in query expression.

any ideas?

 

[dhookom]

Is IssueDate ever Null?
Have you tried to simplify the expression by removing the "..And Format(......)" part?
=Sum(Abs(PermitPurpose = "New" ))



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[monkeysee]

Duane,
Finally able to get back to my access programming!
You were right, I had some null values in this field, which shouldn't have. After I fixed that, it works fine.

Except, how do we have the user choose the date period they wish to generate a report on?

Also, getting back to the null value, I do have another field that may have a null value (which should not be counted) ie: inspection date. When using this formula I'm not getting a count. Here is what I am using:
=Sum(Abs([PermitPurpose]="New" And Format([SepticInspDate],"yyyq")=Format(Date(),"yyyq")))

Thanks for your great help & knowlege!
Deb

 

[dhookom]

If SepticInspDate might be null, you will need to convert it to a non-null value.
=Sum(Abs([PermitPurpose]="New" And Format(Nz([SepticInspDate],Date()+200),"yyyyq")=Format(Date(),"yyyyq")))

To filter a report to a specific date range, I always have the user enter the dates into form controls and then open the report using a "where" clause based on the control values.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.