COUNTIF in Access

[Guest]

Firstly, please forgive a novice!
I have a spreadsheet which captures activities with various customers. Once
of the field's is 'Meeting Date' and has various dates throughout this year
in the format 23/08/2007. I'm putting together a report of these activitied
and one of my tasks is to show how many meetings we have had with customer
per month.
Not knowing where to start I thought that a countif would be good and
scrolling through other answers I came across:
CondCount: IIF([Field] > 5, 1, 0) - for a count of anything over 5.

I tried to amend this myself in a query and changed to:
CondCount: IIF(31/07/2007>[MeetingDate] >01/07/2007, 1, 0) -Counting all
meeting dates that fell in July!

This isn't working and am wondering if anyone could suggest a solution?
Thanks in advance....
Kate

 

[Guest]

Apologies but have used the word 'spreadsheet' where I mean 'database'. Have
been on Excel all day!!

 

[Douglas J. Steele]

CondCount: IIF([MeetingDate] >= #07/01/2007# AND [MeetingDate] <=
#07/31/2007#, 1, 0)

1. You cannot short-circuit the comparison as you were trying to do.

2. Literal Date constants must be delimited with # characters and must be in
a format Access will recognize. Regardless of what your Short Date format
may be under Regional Settings, Access will only recognize dd/mm/yyyy if it
has to. In other words, to Access 01/07/2007 will always be 07 Jan, 2007.
Only because there isn't a 31st month will it correctly recognize 31/07/2007
as 31 July, 2007. For more on dates, take a look at what Allen Browne has in
"International Dates in Access" at http://allenbrowne.com/ser-36.html or
what I had in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

It's possible that since you're using this in a query, you might be able to
use

CondCount: IIF([MeetingDate] BETWEEN #07/01/2007# AND #07/31/2007#, 1, 0)

 

[Guest]

Thanks very much for this - it worked a treat.

Kate

CondCount: IIF([MeetingDate] >= #07/01/2007# AND [MeetingDate] <=
#07/31/2007#, 1, 0)

1. You cannot short-circuit the comparison as you were trying to do.

2. Literal Date constants must be delimited with # characters and must be in
a format Access will recognize. Regardless of what your Short Date format
may be under Regional Settings, Access will only recognize dd/mm/yyyy if it
has to. In other words, to Access 01/07/2007 will always be 07 Jan, 2007.
Only because there isn't a 31st month will it correctly recognize 31/07/2007
as 31 July, 2007. For more on dates, take a look at what Allen Browne has in
"International Dates in Access" at http://allenbrowne.com/ser-36.html or
what I had in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

It's possible that since you're using this in a query, you might be able to
use

CondCount: IIF([MeetingDate] BETWEEN #07/01/2007# AND #07/31/2007#, 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Firstly, please forgive a novice!
I have a spreadsheet which captures activities with various customers.
Once
of the field's is 'Meeting Date' and has various dates throughout this
year
in the format 23/08/2007. I'm putting together a report of these
activitied
and one of my tasks is to show how many meetings we have had with customer
per month.
Not knowing where to start I thought that a countif would be good and
scrolling through other answers I came across:
CondCount: IIF([Field] > 5, 1, 0) - for a count of anything over 5.

I tried to amend this myself in a query and changed to:
CondCount: IIF(31/07/2007>[MeetingDate] >01/07/2007, 1, 0) -Counting all
meeting dates that fell in July!

This isn't working and am wondering if anyone could suggest a solution?
Thanks in advance....
Kate