Month-to-date

Use criteria like the following against your date field to report the current
month:
Between DateSerial(Year(Date()),Month(Date()),1)
and DateSerial(Year(Date()),Month(Date())+1,0)

If you wish you can simplify that a bit to
Between DateSerial(Year(Date()),Month(Date()),1) and Date()

Either of the above will give you the desired results unless your date field
contains a date and time. In that case, you will drop the last day's events
unless the event occurred at exactly midnight.

If that is the case then you need criteria like:
= DateSerial(Year(Date()),Month(Date()),1)
and DateSerial(Year(Date()),Month(Date())+1,1)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

karim wrote:
Daryl
Thanks for the fast replay. I don't know how to restrict the date criteria
to show the monthly result without me going in and changing it every month.
The data I have is date, type of incident, notes. So what I'm trying to do is
for the month of November, when you open the dashboard, it shows you have 10
near miss, and 2 accidents. then when it's December, you go it and it shows 1
near miss and 0 accidents...and so on....

Thanks.

"Daryl S" wrote:

Karim -

If you have a date field as part of your data, then you can write a query to
restrict the result set to any date criteria you wish. If you need more
help, you will need to post more information, like what data you have and how
the calculations should work.

--
Daryl S


"karim" wrote:

Hello All,
I am working on a safety database and I was asked to make a dashboard that
shows all near misses for the month. My question is, is there a way to make
the qry calculate the near misses on the current month only?

Thanks for all the help.

Thank you all for the help, all great info.

I am not trying to calculate the near misses, I am trying to find the near
misses in the current month. The way Al menssioned does work great, but I
want the form to do the calculation by itself when it is opened. I don't want
the user to type dates or anything. They just open the form and there would
be a label saying "Month-to-Date near miss" and it updates the number of the
near misses as we go. but then when a new month comes, on the 1st there would
be "0" near misses.

Thank you all.

Thank you all for the help, all great info.

I am not trying to calculate the near misses, I am trying to find the near
misses in the current month. The way Al menssioned does work great, but I
want the form to do the calculation by itself when it is opened. I don't want
the user to type dates or anything. They just open the form and there would
be a label saying "Month-to-Date near miss" and it updates the number of the
near misses as we go. but then when a new month comes, on the 1st there would
be "0" near misses.

Thank you all.

Thank you all for the help, all great info.

I am not trying to calculate the near misses, I am trying to find the near
misses in the current month. The way Al menssioned does work great, but I
want the form to do the calculation by itself when it is opened. I don't want
the user to type dates or anything. They just open the form and there would
be a label saying "Month-to-Date near miss" and it updates the number of the
near misses as we go. but then when a new month comes, on the 1st there would
be "0" near misses.

Thank you all.

in another way:

Month To Date: = DateSerial(Year(Date()), Month(Date()), 1)


This code is what I need, but it's not working in the criteria...

in another way:

Month To Date: = DateSerial(Year(Date()), Month(Date()), 1)


This code is what I need, but it's not working in the criteria...

in another way:

Month To Date: = DateSerial(Year(Date()), Month(Date()), 1)


This code is what I need, but it's not working in the criteria...

On Tue, 1 Dec 2009 08:41:01 -0800, karim
wrote:

in another way:

Month To Date: = DateSerial(Year(Date()), Month(Date()), 1)


This code is what I need, but it's not working in the criteria...

You should not be putting this expression as a calculated field, but instead
as a criterion on the Criteria line under the date field in your table.

--

John W. Vinson [MVP]

On Tue, 1 Dec 2009 08:41:01 -0800, karim
wrote:

in another way:

Month To Date: = DateSerial(Year(Date()), Month(Date()), 1)


This code is what I need, but it's not working in the criteria...

You should not be putting this expression as a calculated field, but instead
as a criterion on the Criteria line under the date field in your table.

--

John W. Vinson [MVP]

On Tue, 1 Dec 2009 08:41:01 -0800, karim
wrote:

in another way:

Month To Date: = DateSerial(Year(Date()), Month(Date()), 1)


This code is what I need, but it's not working in the criteria...

You should not be putting this expression as a calculated field, but instead
as a criterion on the Criteria line under the date field in your table.

--

John W. Vinson [MVP]