Query criteria catch-22

[KerryL]

My summary report requires me to display number of entries made for the month and for the year-to-date.

If my report source is a query that filters by month (and use =Count for the report field) then my YTD numbers will be wrong because they're filtered out. OTOH, if I query the entire table, then the YTD numbers are fine but I can't get a count for monthly only.

How do I create a query with a YTD filter AND a Monthly filter without one affecting the other count? Or do I have to use separate queries, and if so, how would I then use 2 queries as sources for one report?

Basically, from one table containing all entries I need to be able to show YTD counts and Monthly counts together on the same report.

 

[traingamer]

Select your data for the full date range, counting the YTD values as you have been and add another column

count(iif(yourdate=currmonth, 1, 0))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

or

sum(iif(yourdate=currmonth, 1, 0))

might work better.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

So I should use a query that queries the entire table so I can get YTD numbers, then add columns with code/criteria that produces the calculations I need for the monthly and other range-specific data? Or should I use the table as the record source?


FWIW, I used your code for criteria in a new column but when I run the query I get this error:
"Cannot have aggregate function in WHERE clause."

Here's my new column info:
Field = TEST:EntryDate
Table = tblWARNData
Criteria = Sum(IIf("TEST"="currmonth",1,0))

BTW, in reality this column result needs to equal the number of records within a date range entered by the user in frmRptCritera.txtBeginDate & frmRptCriteria.txtEndDate

 

[traingamer]

It's not supposed to be in the where clause - it's another data column.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

I apologize for my confusion.

I added another data column with this expression in the Field:
Expr1: (IIf("EntryDate"="CurrMonth",1,0))

In the Total box I selected Sum

When I run the query I get all the table records. In the Expr1 field of every record is a zero. There seems to be no sum of the records that were entered in February.

 

[AlexCuse]

unless I am missing something, you need to replace "CurrMonth" with something like this:

DatePart("m",(Date()))

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[traingamer]

You probably just need to do something like Month([EntryDate]) = Month(Date())

"EntryDate" and "CurrMonth" are strings that will never be equal.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

Thank you both, I think I finally got it to work with this expression:

PeriodNotice: (IIf(Month([EntryDate])=Month(Date()),1,0))

Now I can count the records with "1" in the PeriodNotice field and show that total on the report.

Sorry for the trouble I was having. Thanks again,
KerryL