Dcount in report with 2 criteria only works individually, not with

  • Thread starter Thread starter alexs
  • Start date Start date
A

alexs

Hi,

I am trying to generate a count based on 2 criteria. The expression works
properly when I use either of the criteria on its own but when I combine with
the AND, the results shows a total which is equal to all records.

This expression is based on 2 fields in the same query (one contains a full
date and the other has nationality). I'm simply trying to count records for
those members who joined last year that have "united states" for their
nationality. I'm not hard coding the date so that this can be run in future
years for the previous year.

I am sure my combined formula is wrong but I cannot seem to figure this one
out - here is my current expression:

=DCount("*","all membership query","DatePart('yyyy',[All Membership
Query]![DATE_JOINED])=Year(Date())-1" And "[All Membership
Query]![nationality family]='united states'")

Thanks for any hints...
 
You have the AND outside the quotes.

Try something like this as the Criteria argument:
"(Year([Date_Joined) = Year(Date()) - 1) AND ([nationality family]='united
states')"
 
Hi Allen, thank you very much for the fast response - your logic fixed my
problem right away.

Cheers!
Alex

Allen Browne said:
You have the AND outside the quotes.

Try something like this as the Criteria argument:
"(Year([Date_Joined) = Year(Date()) - 1) AND ([nationality family]='united
states')"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

alexs said:
I am trying to generate a count based on 2 criteria. The expression works
properly when I use either of the criteria on its own but when I combine
with
the AND, the results shows a total which is equal to all records.

This expression is based on 2 fields in the same query (one contains a
full
date and the other has nationality). I'm simply trying to count records
for
those members who joined last year that have "united states" for their
nationality. I'm not hard coding the date so that this can be run in
future
years for the previous year.

I am sure my combined formula is wrong but I cannot seem to figure this
one
out - here is my current expression:

=DCount("*","all membership query","DatePart('yyyy',[All Membership
Query]![DATE_JOINED])=Year(Date())-1" And "[All Membership
Query]![nationality family]='united states'")

Thanks for any hints...
Click to expand...
Click to expand...
 
If you are counting within the records of the report, it is more efficient to
use:
=Abs(Sum(Year([Date_Joined) = Year(Date()) - 1 AND [nationality
family]="united states"))
--
Duane Hookom
Microsoft Access MVP


alexs said:
Hi Allen, thank you very much for the fast response - your logic fixed my
problem right away.

Cheers!
Alex

Allen Browne said:
You have the AND outside the quotes.

Try something like this as the Criteria argument:
"(Year([Date_Joined) = Year(Date()) - 1) AND ([nationality family]='united
states')"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

alexs said:
I am trying to generate a count based on 2 criteria. The expression works
properly when I use either of the criteria on its own but when I combine
with
the AND, the results shows a total which is equal to all records.

This expression is based on 2 fields in the same query (one contains a
full
date and the other has nationality). I'm simply trying to count records
for
those members who joined last year that have "united states" for their
nationality. I'm not hard coding the date so that this can be run in
future
years for the previous year.

I am sure my combined formula is wrong but I cannot seem to figure this
one
out - here is my current expression:

=DCount("*","all membership query","DatePart('yyyy',[All Membership
Query]![DATE_JOINED])=Year(Date())-1" And "[All Membership
Query]![nationality family]='united states'")

Thanks for any hints...
Click to expand...
Click to expand...
Click to expand...
 
Duane's suggestion is also more reliable (e.g. if a Filter is applied to the
report.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Duane Hookom said:
If you are counting within the records of the report, it is more efficient
to
use:
=Abs(Sum(Year([Date_Joined) = Year(Date()) - 1 AND [nationality
family]="united states"))
--
Duane Hookom
Microsoft Access MVP


alexs said:
Hi Allen, thank you very much for the fast response - your logic fixed my
problem right away.

Cheers!
Alex

Allen Browne said:
You have the AND outside the quotes.

Try something like this as the Criteria argument:
"(Year([Date_Joined) = Year(Date()) - 1)
AND ([nationality family]='united states')"
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane, thanks for the suggestion- I will try that as well and see how it
fairs.

Thanks kindly for the expert guidance!

Alex

Duane Hookom said:
If you are counting within the records of the report, it is more efficient to
use:
=Abs(Sum(Year([Date_Joined) = Year(Date()) - 1 AND [nationality
family]="united states"))
--
Duane Hookom
Microsoft Access MVP


alexs said:
Hi Allen, thank you very much for the fast response - your logic fixed my
problem right away.

Cheers!
Alex

Allen Browne said:
You have the AND outside the quotes.

Try something like this as the Criteria argument:
"(Year([Date_Joined) = Year(Date()) - 1) AND ([nationality family]='united
states')"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am trying to generate a count based on 2 criteria. The expression works
properly when I use either of the criteria on its own but when I combine
with
the AND, the results shows a total which is equal to all records.

This expression is based on 2 fields in the same query (one contains a
full
date and the other has nationality). I'm simply trying to count records
for
those members who joined last year that have "united states" for their
nationality. I'm not hard coding the date so that this can be run in
future
years for the previous year.

I am sure my combined formula is wrong but I cannot seem to figure this
one
out - here is my current expression:

=DCount("*","all membership query","DatePart('yyyy',[All Membership
Query]![DATE_JOINED])=Year(Date())-1" And "[All Membership
Query]![nationality family]='united states'")

Thanks for any hints...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top