Solved Count using Form date Parameters

[EM2021]

Why does the follow sum calculation work but not count within the Q1 parameters? The Count gives me total in my query and ignores the Q1 parameter.

=Sum(IIf([Dateasked] Between [Forms]![Prospect Reports Manager]![Q2Start] And [Forms]![Prospect Reports Manager]![Q2End],[Amountasked],0))

=Count(IIf([Dateasked] Between [Forms]![Prospect Reports Manager]![Q2Start] And [Forms]![Prospect Reports Manager]![Q2End],[Amountasked],0))

 

[arnelgp]

always passed Null for the False condition.

=Count(IIf([Dateasked] Between [Forms]![Prospect Reports Manager]![Q2Start] And [Forms]![Prospect Reports Manager]![Q2End],"1", Null))

 

[theDBguy]

Hi. Welcome to AWF!

You can also use Sum() to count.

=Sum(IIf(Condition=True,1,0))

 

[EM2021]

always passed Null for the False condition.

=Count(IIf([Dateasked] Between [Forms]![Prospect Reports Manager]![Q2Start] And [Forms]![Prospect Reports Manager]![Q2End],"1", Null))

This worked! Thank you! Can you explain exactly what you are saying with "1"?

 

[arnelgp]

"1" is a Constant.
query will not findout the "value" of it since it is a constant.
if you use a Fieldname, the query will doubly check if it is null or not
and include it in Count if it finds out that it is not Null value.

 

[EM2021]

"1" is a Constant.
query will not findout the "value" of it since it is a constant.
if you use a Fieldname, the query will doubly check if it is null or not
and include it in Count if it finds out that it is not Null value.

Wonderful, thanks