Query By Month and Year - Access 2007



I have a table with contribution information, with a date field for posting
the date of the contribution. I would like to query this information by
month and year.

I first used the following:

=[Start Date] And <=[End Date]

which works pretty well, but I would like to make it easier for the user so
the only have to input a month and year. So, I further refinded this by
using:

Between DateSerial(Year(Date()), [Enter month NUMBER (1-12)],1) AND
DateSerial(Year(Date()),1+ [Enter month NUMBER (1-12)],0)

That worked great, except it only gives me the current year. So if I enter
"12" I only get information for December 2008. After further investigation,
I found this code in another post here (I entered my own table and field
names). I added this code in SQL view, but keep getting error (syntax)
messages when I try to save it

PARAMETERS TheYear Long, TheMonth Long;
SELECT tblReceipts.[ContributionDate]
FROM tlbReceipts
WHERE (([ContributionDate] >= DateSerial(TheYear, TheMonth, 1))
And ([ContributionDate] < DateSerial(TheYear, TheMonth + 1, 1)))

I've copied the text from the SQL view here:

SELECT tblContributor.ContributorID,
tblContributor.[LastName/OrganizationName], tblContributor.FirstName,
tblReceipts.ContributionDate, tblReceipts.Amount
FROM tblContributor INNER JOIN tblReceipts ON tblContributor.ContributorID =
tblReceipts.ContributorID
WHERE (((tblReceipts.ContributionDate) Between
DateSerial(Year(Date()),[Enter month NUMBER (1-12)],1) And
DateSerial(Year(Date()),1+[Enter month NUMBER (1-12)],0)));

PARAMETERS TheYear Long, TheMonth Long;
SELECT tblReceipts.[ContributionDate]
FROM tlbReceipts
WHERE (([ContributionDate] >= DateSerial(TheYear, TheMonth, 1))
And ([ContributionDate] < DateSerial(TheYear, TheMonth + 1, 1)));

The error says: Syntax error (missing operator) in query expression
'(((tblReceipts.ContributionDate) Between DateSerial(Date()),[Enter month
NUMBER (1-12)],1) And DateSerial(Year(Date()),1+[Enter month NUMBER
(1-12)],0)))

I've tried deleting the first WHERE statement because I realize it's
redundant, but that doesn't work either.

In addition, will this solution work if I want to query by YEAR, so that I
get the information for a certain year (the whole year)?

Thanks!

.



Relevant Pages

  • Re: Query by Month and Year (Repost)
    ... I have a table with contribution information, with a date field for posting ... FROM tlbReceipts ... I've copied the text from the SQL view here. ... Syntax error in query expression ...
    (microsoft.public.access.queries)
  • Query by Month and Year (Repost)
    ... I am reposting this question because I cannot access the original question ... I have a table with contribution information, with a date field for posting ... Syntax error in query expression ... In addition, will this solution work if I want to query by YEAR, so that I ...
    (microsoft.public.access.queries)
  • RE: displaying data for current week
    ... I wasn't able to make your solution work ... This is the expression I am trying to incorporate in the query criteria. ... earlier use of parentheses in the table name isn't creating the hiccup. ... month in a child linked subform? ...
    (microsoft.public.access.forms)
  • Re: Query By Month and Year - Access 2007
    ... I added this code in SQL view, ... PARAMETERS TheYear Long, TheMonth Long; ... FROM tlbReceipts ... Syntax error in query expression ...
    (microsoft.public.access.queries)
  • Re: Write the first digit, only
    ... Sorry for my bad explanation but the your solution work ... >prompt you for the first digit when you run the query? ... >I have records with numeric IdNum and others fields. ...
    (microsoft.public.access.queries)