Records for a particular month

[Mellstock]

I am running Access 2000 and Windows XP. What I would like to do is to
is display all the records for a given month and year eg March 2008.
At the moment I do it via a parameter query entering a start and
finish date.

I did ask this question some time ago and Ken Snell replied with the
following suggestion,

SELECT * FROM TableName
WHERE Month([DateFieldName]) = [Enter month number:] AND
Year([DateFieldName]) = [Enter year (yyyy):];

When I put this into the criteria I am told I have written a sub query
that can return more than one field without using the EXISTS reserved
word in the FROM clause.

I suspect I am not using this expression correctly. How should I be
using it ? and is this the easiest way of achieving what I need.

Thank you

 

[Rick Brandt]

I am running Access 2000 and Windows XP. What I would like to do is to
is display all the records for a given month and year eg March 2008.
At the moment I do it via a parameter query entering a start and
finish date.

I did ask this question some time ago and Ken Snell replied with the
following suggestion,

SELECT * FROM TableName
WHERE Month([DateFieldName]) = [Enter month number:] AND
Year([DateFieldName]) = [Enter year (yyyy):];

When I put this into the criteria I am told I have written a sub query
that can return more than one field without using the EXISTS reserved
word in the FROM clause.

I suspect I am not using this expression correctly. How should I be
using it ? and is this the easiest way of achieving what I need.

That is not "criteria". That is the SQL of the entire query. Only the part
after the word "WHERE" is the criteria.

 

[John W. Vinson]

I suspect I am not using this expression correctly. How should I be
using it ? and is this the easiest way of achieving what I need.

Ken was posting the entire query, in SQL the language of queries; the query
grid is not the query itself, just a tool to build SQL. Try using View... SQL
on some of your queries to get familiar with how SQL works; it's the "lingua
franca" of communicating queries on these newsgroups!

In your case you can either create two calculated fields by typing

Month([DateFieldName])

in a vacant Field cell in your query, and putting

[Enter month number:]

on its criteria line, and typing

Year([DateFieldName])

in a second column, with a criterion of

[Enter year (yyyy):]

Or, a bit more efficiently, use a criterion on your datefield of

= DateSerial([Enter year (yyyy):], [Enter month number:], 1) AND < DateSerial([Enter year (yyyy):], [Enter month number:] + 1, 1)

 

[Mellstock]

I suspect I am not using this expression correctly. How should I be
using it ? and is this the easiest way of achieving what I need.

Ken was posting the entire query, in SQL the language of queries; the query
grid is not the query itself, just a tool to build SQL. Try using View... SQL
on some of your queries to get familiar with how SQL works; it's the "lingua
franca" of communicating queries on these newsgroups!

In your case you can either create two calculated fields by typing

Month([DateFieldName])

in a vacant Field cell in your query, and putting

[Enter month number:]

on its criteria line, and typing

Year([DateFieldName])

in a second column, with a criterion of

[Enter year (yyyy):]

Or, a bit more efficiently, use a criterion on your datefield of

= DateSerial([Enter year (yyyy):], [Enter month number:], 1) AND < DateSerial([Enter year (yyyy):], [Enter month number:] + 1, 1)

Thank you seems to work a treat. I will have to get my head around
DateSerial and the significance of the "ones" to work how it works.
Thanks again.

 

[John W. Vinson]

= DateSerial([Enter year (yyyy):], [Enter month number:], 1) AND < DateSerial([Enter year (yyyy):], [Enter month number:] + 1, 1)

Thank you seems to work a treat. I will have to get my head around
DateSerial and the significance of the "ones" to work how it works.

DateSerial takes three arguments: a year, a month, and a day of the month.
That is, DateSerial(2008,5,26) would return today's date.

The expression

DateSerial([Enter year (yyyy):], [Enter month number:], 1)

prompts the user for a year and a month, and the 1 gives you the first day of
that month.

DateSerial([Enter year (yyyy):], [Enter month number:] + 1, 1) adds 1 to the
month number to give you the first day of the NEXT month.

So if the user enters 2008 and 5, the first expression returns #2008-05-01#
and the second expression returns #2008-06-01#; the criterion finds all dates
greater than or equal to May 1, and less than June 1 (up to but not
including).

The function is clever enough that if you give it a month number 13 it will
correctly give you January of the next year.

 

[Mellstock]

= DateSerial([Enter year (yyyy):], [Enter month number:], 1) AND < DateSerial([Enter year (yyyy):], [Enter month number:] + 1, 1)

Thank you seems to work a treat. I will have to get my head around
DateSerial and the significance of the "ones" to work how it works.

DateSerial takes three arguments: a year, a month, and a day of the month.
That is, DateSerial(2008,5,26) would return today's date.

The expression

DateSerial([Enter year (yyyy):], [Enter month number:], 1)

prompts the user for a year and a month, and the 1 gives you the first dayof
that month.

DateSerial([Enter year (yyyy):], [Enter month number:] + 1, 1) adds 1 to the
month number to give you the first day of the NEXT month.

So if the user enters 2008 and 5, the first expression returns #2008-05-01#
and the second expression returns #2008-06-01#; the criterion finds all dates
greater than or equal to May 1, and less than June 1 (up to but not
including).

The function is clever enough that if you give it a month number 13 it will
correctly give you January of the next year.

Thank you for the very clear explanation. Clearly a very useful
function and well worth knowing about thank you.