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

[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...

 

[Allen Browne]

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')"

 

[alexs]

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

Cheers!
Alex

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...

 

[Duane Hookom]

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

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

Cheers!
Alex

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...

 

[Allen Browne]

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.

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

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

Cheers!
Alex

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')"

 

[alexs]

Hi Duane, thanks for the suggestion- I will try that as well and see how it
fairs.

Thanks kindly for the expert guidance!

Alex

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

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

Cheers!
Alex

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...