Counting rows for current month

[CMLS]

Hello

I am having problems creating a query that shows the row count for the current month from a table.

Although I can do both seperately I'm not able to create a single query without an error message.

Here's the query that currently works but needs to only show current months records.

SELECT Count(*) AS DealsIn
FROM EventLog
WHERE (((EventLog.EventType)='Status') AND ((EventLog.Event)='5'));

Any help would be appreciated.

Thank you

 

[pbaldy]

Try

WHERE EventLog.EventType='Status' AND EventLog.Event='5' AND DateField Between #10/1/10# And #10/31/10#

You can use form fields or formulas for the dates.

 

[CMLS]

Thanks for the prompt reply Paul.

I should have said that I need it to dynamically change to the current month so I don't have to change it.

)

 

[pbaldy]

Like I said, you can replace the hard coded dates with formulas:

http://www.pacificdb.com.au/Support/code_date_monthfunctions.htm

 

[CMLS]

Hi Paul

I maybe being thick here, hold those comments, but I can't see a formula for the currrent month, i.e. BETWEEN [first day of current month] AND [last day of current month].

Thanks again
Richard

 

[JohnLee]

Hi,

in addition to your date field in the query create another column and enter the following against the rows shown:

Field: Month:Month([dtmDate]) 'dtmDate being your date field
Criteria: Month(Now())

make sure you remove any criteria you have in the criteria cell beneath your date field, but keep your date field intact this way when you run your query you will always have the current month and be able to see the individual dates as well

I hope this helps.

John

 

[CMLS]

Thank you, thank you, thank you, it works a treat!

I was playing around with the Month(Now()) function and just wasn't able to get it working.

Cheers
Richard

 

[JohnLee]

Hi Richard,

Your welcome, glad it's working.

John

 

[pbaldy]

That's not going to work over the long haul, when you have multiple years of data. You didn't see the formulas for "First day of a specified month" and "Last day of a specified month"? Feed those the Date() function and you have your two dates.

 

[JohnLee]

Hi Pbaldy,

I've been using that formula for a couple of years now in my accounts data base and I've never encountered a problem with it. It always gives me the data for the current month inclusive of the 1st and last day of that month.

obviously if you run the query at any time before the end of the month, you only get data up to that point for that month.

based on what Richard asked for, which I understood to month specific this formula does the job, unless I've missed something.

John

 

[Dairy Farmer]

*** First day of month ***
DateSerial(Year(Now()), Month(Now()), 1)
 
*** Last day of month ***
DateSerial(Year(Now()), Month(Now()) + 1, 0)

Relace Now() as needed.

Suggest that you have a ComboBox that gets the months from your table.
Month: Format$([Table1]![Date1],'mmmm yyyy')

A second query will use what is in the ComboBox to filter. In the second query add a field:
Month: Format$([Table1]![Date1],'mmmm yyyy')

In criteria for Month:
[Forms]![Form1]![ComboBox]

 

[pbaldy]

The Month() function simply returns 10. That formula will find everything in October, but it will not differentiate between years. You'll get October 2009, 2010, 2011, etc. To use that method, you'd have to include an additional field using Year().

 

[JohnLee]

Hi Pbaldy,

Yes of course, as I also have criteria in my database that ensures it's the current year as well. But as Richard was asking specifically about being month specific I didn't focus on that aspect.

John