Countiif Between Dates (1 Viewer)

JithuAccess

Member
Local time
Today, 16:31
Joined
Mar 3, 2020
Messages
297
Hello Guys,

I want to get the total count based on two criterias. The first criteria is Country and the Second criteria is Date Received. The date received should be 7 Days prior to Today's Date. Today's date is 02-Mar-2021 and I want to find the total count between the dates between 23-Feb-2021 and 02-Mar-2021. I have put this Function in the Control Source Property of a Text Box


=count(iif([strCountry="USA" And [datReceived Date]=between Date() And Date()-7,1))

I am getting an Error. I think the syntax is wrong. Could you please help me to fix this?

Thanks
 

Minty

AWF VIP
Local time
Today, 22:31
Joined
Jul 26, 2013
Messages
10,355
You Are missing a closing square bracket after strCountry ]
 

Minty

AWF VIP
Local time
Today, 22:31
Joined
Jul 26, 2013
Messages
10,355
It may be better / easier to use a DCount() based on the forms underlying table or query.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:31
Joined
Sep 21, 2011
Messages
14,048
You do not use = and BETWEEN ?
You need the ] after strCountry as Minty mentioned.
I'd also have a 0 if false.?
 

JithuAccess

Member
Local time
Today, 16:31
Joined
Mar 3, 2020
Messages
297
You do not use = and BETWEEN ?
You need the ] after strCountry as Minty mentioned.
I'd also have a 0 if false.?

Many Thanks. I have changed the Function like this and it's working perfect.


=Count(IIf([strCountry]="USA" And [datReceived Date] Between Date() And Date()-7,0))

Thanks a lot for your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:31
Joined
Oct 29, 2018
Messages
21,358
Many Thanks. I have changed the Function like this and it's working perfect.


=Count(IIf([strCountry]="USA" And [datReceived Date] Between Date() And Date()-7,0))

Thanks a lot for your help
Hi. Glad to hear you got it working, but I think Count() is really the wrong function for this. Good luck though!
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 22:31
Joined
Sep 21, 2011
Messages
14,048
Many Thanks. I have changed the Function like this and it's working perfect.


=Count(IIf([strCountry]="USA" And [datReceived Date] Between Date() And Date()-7,0))

Thanks a lot for your help
No, I'd say that is still incorrect? You have now replaced the value of 1 for True to be 0 ?

Code:
=Count(IIf([strCountry]="USA" And [datReceived Date] Between Date() And Date()-7,1,0))
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:31
Joined
Feb 19, 2002
Messages
42,976
Count() counts the number of rows in the set. If you want to create a set, you probably need to use DCount() where you can specify a where clause.

You could use Sum() rather than count to solve your problem. The IIF returns 1 if the IIf is true and 0 if it is false and sum() sums the 1's and 0's.

=Sum(iif([strCountry="USA" And [datReceived Date]=between Date() And Date()-7) 1,0)
 

Users who are viewing this thread

Top Bottom