Excluding the Year

R

rokjock

I have built a database that keeps track of appointments. From this I would
like to pull a variety of Metrics. One of the metrics that I would like to
pull is :
HOW MANY APPOINTMENTS PER MONTH DID I HAVE. I am having trouble creating
this query as a direct result of the dates being entered with the year as
well. Is there any way to exclude the year and just look at the month when
I create my query? Any help will be very much appreciated;-)
 
R

rokjock

I did as you suggested but no real help was discovered. ANy other
thoughts? Thank you again for your input......
 
K

Ken Snell [MVP]

Post an example of the data that are in this field. Is the calculation to
just be based on month, ignoring which year it was in, or is it to be based
on the combination of the month and the year?
 
R

rokjock

It is just to pull out data for certain months barring the year. For
example: How many appointments came in during the month of Jan. I would
like to be able to develop a query that will answer that question for every
month. The month is the most important aspect. I just cant figure out how
to tell access to ignore the year and to just focus on the month. Thank you
very much for your time!!
 
K

Ken Snell [MVP]

Are you storing a date in that field? Or is it a text string of the month
and year? See my question in my last reply.

You need to tell us this info before we can suggest another way of getting
you the result that you seek.
 
J

John Vinson

It is just to pull out data for certain months barring the year. For
example: How many appointments came in during the month of Jan. I would
like to be able to develop a query that will answer that question for every
month. The month is the most important aspect. I just cant figure out how
to tell access to ignore the year and to just focus on the month. Thank you
very much for your time!!
Click to expand...

Put a calculated field in your Query:

MonthNo: Month([datefield])

If datefield is the name of a Date/Time field in your table (which is
what Ken has been trying to get you to answer) this calculated field
will contain 1 for all dates that fall in any January, 2 for February,
.... 12 for December. You can use it as a field to be queried, sorted,
grouped by etc. (anything except edited).

John W. Vinson[MVP]
(no longer chatting for now)
 
J

John Vinson

Sorry I did guess I did not understand what yo were asking. I have attached
a snapshot of my table. Hopefully that will help clear things up a bit.
Thanks again for all of you input
Click to expand...

Sorry. I am reluctant to open unvouched binary files which could
contain code.

Please use the keyboard, or copy and paste, to *answer Ken's very
reasonable question*. Do you have the date in an Access Date/Time
field? or a Text field? If text, what's in it? A date, or just a
character string such as "Apr"?

John W. Vinson[MVP]
(no longer chatting for now)
 
R

rokjock

It is an access date and time field (not text)
John Vinson said:
Sorry. I am reluctant to open unvouched binary files which could
contain code.

Please use the keyboard, or copy and paste, to *answer Ken's very
reasonable question*. Do you have the date in an Access Date/Time
field? or a Text field? If text, what's in it? A date, or just a
character string such as "Apr"?

John W. Vinson[MVP]
(no longer chatting for now)
Click to expand...
 
K

Ken Snell [MVP]

Because the field is a date/time type, Jeff's original suggestion of using
the Month function in a calculated field is your solution. You then can use
that calculated field to sort, group, filter, etc.

Example:

MonthValue: Month([NameOfDateTimeField])
 
Top