Month-to-date

[karim]

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.

 

[Daryl S]

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.

 

[Al Campagna]

karim,
Not sure what you mean by "calculate the near misses."
If you mean a Count of all near misses for a date range, then it's just
a matter of creating a Totals query, and setting a date range criteria in
your query... against the field in your table that indicates the date
of each "near miss."

Create a query that counts all the "near misses"... no
matter what date/s...
Then you criteria that query with a changeable date range...
Ex. field...
NearMissDate

= [Beginning Date] and <= [EndingDate]

With the above criteria, when the query is run, you will be asked
(via an input box) for a [Beginning Date] value. Then you will be asked
for an [Ending Date].
Say you enter 1/1/09 and 2/1/09 in response...
The query should run, and only return a count of "near misses" within
that date range.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[karim]

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.

 

[John Spencer]

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]

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.

 

[karim]

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...

 

[John W. Vinson]

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.

 

[Daryl S]

Karim -

Can you explain what you mean by 'not working'? Today is the 1st of the
month, so I would expect no results until a near miss for December is added.

Are you getting an error message?

 

[karim]

yes, I am getting an error msg "#Name?"

I also added a record for today just to try it.

Basically what I am doing is I made a form and I have a text box that
suppose to show the totla records in it, but it's giving me that error.

 

[Al Campagna]

That's probably due to an element of the calculation referring to an
object name that does not exist.
Given Price and Qty then a calculation like...
Prize * Qty = LineTotal
would yield a #Name? error.
Check the spelling in the calc vs. the real object names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Daryl S]

Karim -

If you are using a query, can you post the SQL to your query (copy/paste
from the SQL View)? That will help us see what you are doing. I assume
this is the query behind the form?