Query by Month and Year (Repost)

P

Pam

I am reposting this question because I cannot access the original question
nor the reply.

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 to the current 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. This is what the code looks
like now:

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!
 
M

Marshall Barton

Pam said:
I am reposting this question because I cannot access the original question
nor the reply.

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 to the current 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. This is what the code looks
like now:

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 one 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 want to consider going back to using
a start month/year
and an end month/year.
 
D

Dale Fye

I think what you are looking for is:

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 [ContributionDate] >= DateSerial(TheYear, TheMonth, 1)
And [ContributionDate] < DateSerial(TheYear, TheMonth + 1, 1);

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Pam said:
I am reposting this question because I cannot access the original question
nor the reply.

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 to the current 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. This is what the code looks
like now:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query By Month and Year - Access 2007 2
Year to Date subquery 3
Previous Month Query 5
Modifying my Date Select Query 2
current month / year WHERE 8
Querry by Quarter 7
Fiscal Year Query 1
End of Month: Parameter Query? 3

Top