Query By Month and Year - Access 2007

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

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]
Click to expand...

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!
 
Pam said:
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]
Click to expand...

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.
Click to expand...

What do you mean by the FIRST where statement. A basic
Select query can only have one Where clause. It kind of
sounds like you have two queries in on SQL view.

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)?
Click to expand...


No. You would have to use a different approach to enter
just a year. You might go back to using a start month/year
and an end month/year.
 
PARAMETERS TheYear Long, TheMonth Long;
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(TheYear,TheMonth,1) And
DateSerial(TheYear,1+TheMonth,0)

To get fancy you can use the NZ function and change the where clause to

WHERE tblReceipts.ContributionDate Between
DateSerial(Nz(TheYear,Year(Date())),Nz(TheMonth,1),1) And
DateSerial(NZ(TheYear,Year(Date())),1+NZ(TheMonth,12),0)

If you leave the month blank you will get all the data for the specified
year.

If you leave the year blank you will get all the data for the specified
month of the current year.

If you leave BOTH year and month blank you will get all the data for the
current year.

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

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]
Click to expand...

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!
Click to expand...
 
Back
Top