Querry by Quarter

G

garrett901

Hello All

I’m having a bit of a problem and I was wondering if any of you ACCESS
experts could help me.

What I have is a Query that is fairly simple but I need to be able to pull
the data by Month, Quarter (first quarter, second quarter etc…), and yearly
(Jan 1 thru Dec 31, current year). Right now I have it set up so it will ask
you by month (select a number for the month 1 thru 12).
Example:
Between DateSerial(Year(Date()),[Enter month NUMBER(1-12)],1) And
DateSerial(Year(Date()),1+[Enter month NUMBER(1-12)],0)

I would like the quarterly and yearly option to pop up in a box just like it
does in the example above, I just don’t know how to make that happen, and I
don’t speak SQL.

Eventually I would also like to have an option in the same box (that is
password protected) to delete the entries from the previous year, (this is
2009, delete 2008?). If need be I can jump that hurdle latter on.

Here is the SQL view of my current Query:

SELECT [Project Time].Date, [Project Time].[Time Start], [Project
Time].Technition, [Project Time].[Working On], [Project Time].[Time Stop],
[Project Time].[Minutes Worked On Project], [Project Time].Department
FROM [Project Time]
WHERE ((([Project Time].Date) Between DateSerial(Year(Date()),[Enter month
NUMBER(1-12)],1) And DateSerial(Year(Date()),1+[Enter month
NUMBER(1-12)],0)));

I would like to thank all of you in advance for your help. I usually find
the answers I’m looking for already out there asked previously…….So once
again, thank you all!!!
Jeff G
 
J

John Spencer MVP

The easiest way to handle this is to have TWO parameters so you can enter a
start month and an end month.

WHERE [Project Time].Date
Between DateSerial(Year(Date()),[Enter START month NUMBER(1-12)],1)
And DateSerial(Year(Date()),1+[Enter END month NUMBER(1-12)],0)

That way you can do any time period for the current year you want as long as
the time period is measured in whole months. Months, Quarters, SemiAnnual
periods, entire year.

More flexible would be to have the user enter the start date and end date. If
you wanted you could limit that to entire months.

Between DateSerial(Year([Enter Start Date]), Month([EnterStartDate]),1) and
DateSerial(Year([Enter End Date]), 1+ Month([Enter End Date]),0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
G

garrett901

I like this idea.....I'll give it a try, thanks.....JWG
--
Jeff Garrett
Maintenance Tech III
Milgard Tempering


John Spencer MVP said:
The easiest way to handle this is to have TWO parameters so you can enter a
start month and an end month.

WHERE [Project Time].Date
Between DateSerial(Year(Date()),[Enter START month NUMBER(1-12)],1)
And DateSerial(Year(Date()),1+[Enter END month NUMBER(1-12)],0)

That way you can do any time period for the current year you want as long as
the time period is measured in whole months. Months, Quarters, SemiAnnual
periods, entire year.

More flexible would be to have the user enter the start date and end date. If
you wanted you could limit that to entire months.

Between DateSerial(Year([Enter Start Date]), Month([EnterStartDate]),1) and
DateSerial(Year([Enter End Date]), 1+ Month([Enter End Date]),0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello All

I’m having a bit of a problem and I was wondering if any of you ACCESS
experts could help me.

What I have is a Query that is fairly simple but I need to be able to pull
the data by Month, Quarter (first quarter, second quarter etc…), and yearly
(Jan 1 thru Dec 31, current year). Right now I have it set up so it will ask
you by month (select a number for the month 1 thru 12).
Example:
Between DateSerial(Year(Date()),[Enter month NUMBER(1-12)],1) And
DateSerial(Year(Date()),1+[Enter month NUMBER(1-12)],0)

I would like the quarterly and yearly option to pop up in a box just like it
does in the example above, I just don’t know how to make that happen, and I
don’t speak SQL.

Eventually I would also like to have an option in the same box (that is
password protected) to delete the entries from the previous year, (this is
2009, delete 2008?). If need be I can jump that hurdle latter on.

Here is the SQL view of my current Query:

SELECT [Project Time].Date, [Project Time].[Time Start], [Project
Time].Technition, [Project Time].[Working On], [Project Time].[Time Stop],
[Project Time].[Minutes Worked On Project], [Project Time].Department
FROM [Project Time]
WHERE ((([Project Time].Date) Between DateSerial(Year(Date()),[Enter month
NUMBER(1-12)],1) And DateSerial(Year(Date()),1+[Enter month
NUMBER(1-12)],0)));

I would like to thank all of you in advance for your help. I usually find
the answers I’m looking for already out there asked previously…….So once
again, thank you all!!!
Jeff G
Click to expand...
Click to expand...
 
G

garrett901

Well I just gave it a try and it doesn’t work......Hmmmm I copied and
pasted the code and an error box popped up saying that there was a (dot) ! or
prentices in the wrong spot......This must be why I never learned code talk!
:) Back to the drawing board........I do appreciate the help though.......
--
Jeff Garrett
Maintenance Tech III
Milgard Tempering


John Spencer MVP said:
The easiest way to handle this is to have TWO parameters so you can enter a
start month and an end month.

WHERE [Project Time].Date
Between DateSerial(Year(Date()),[Enter START month NUMBER(1-12)],1)
And DateSerial(Year(Date()),1+[Enter END month NUMBER(1-12)],0)

That way you can do any time period for the current year you want as long as
the time period is measured in whole months. Months, Quarters, SemiAnnual
periods, entire year.

More flexible would be to have the user enter the start date and end date. If
you wanted you could limit that to entire months.

Between DateSerial(Year([Enter Start Date]), Month([EnterStartDate]),1) and
DateSerial(Year([Enter End Date]), 1+ Month([Enter End Date]),0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello All

I’m having a bit of a problem and I was wondering if any of you ACCESS
experts could help me.

What I have is a Query that is fairly simple but I need to be able to pull
the data by Month, Quarter (first quarter, second quarter etc…), and yearly
(Jan 1 thru Dec 31, current year). Right now I have it set up so it will ask
you by month (select a number for the month 1 thru 12).
Example:
Between DateSerial(Year(Date()),[Enter month NUMBER(1-12)],1) And
DateSerial(Year(Date()),1+[Enter month NUMBER(1-12)],0)

I would like the quarterly and yearly option to pop up in a box just like it
does in the example above, I just don’t know how to make that happen, and I
don’t speak SQL.

Eventually I would also like to have an option in the same box (that is
password protected) to delete the entries from the previous year, (this is
2009, delete 2008?). If need be I can jump that hurdle latter on.

Here is the SQL view of my current Query:

SELECT [Project Time].Date, [Project Time].[Time Start], [Project
Time].Technition, [Project Time].[Working On], [Project Time].[Time Stop],
[Project Time].[Minutes Worked On Project], [Project Time].Department
FROM [Project Time]
WHERE ((([Project Time].Date) Between DateSerial(Year(Date()),[Enter month
NUMBER(1-12)],1) And DateSerial(Year(Date()),1+[Enter month
NUMBER(1-12)],0)));

I would like to thank all of you in advance for your help. I usually find
the answers I’m looking for already out there asked previously…….So once
again, thank you all!!!
Jeff G
Click to expand...
Click to expand...
 
J

John Spencer MVP

Part of the problem is that you have a field named Date. Date is a reserved
word in Access. Date can mean the current system date and therefore things
can get confused with that.

This is fairly straightforward and should work.

Parameters [Enter Start Date] as DateTime
, [Enter End Date] as DateTime;
SELECT [Project Time].[Date]
, [Project Time].[Time Start]
, [Project Time].Technition
, [Project Time].[Working On]
, [Project Time].[Time Stop]
, [Project Time].[Minutes Worked On Project]
, [Project Time].Department
FROM [Project Time]
WHERE [Project Time].[Date] Between
DateSerial(Year([Enter Start Date]), Month([Enter Start Date]),1) and
DateSerial(Year([Enter End Date]), 1 + Month([Enter End Date]),0)

Sometimes copy and paste from the post will introduce extraneous characters
that cause problems.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
G

garrett901

John,

Thanks for your help.......I haven’t been ignoring the advice I just
haven’t had the time this past week to devote to the project.......Please
forgive my lack of "code" understanding. The code after your sentence, "This
is fairly straightforward and should work." Where would I insert that? My
manipulation of the ACCESS program is fairly limited and mostly don on a
trial and error basis. I would suspect that if an expert like yourself were
to look at my entire database program that I am using (it works quiet well
for us) you would probably think something like, "What the He-Double
toothpicks is this!!!†Once again, thanks for your help...JWG

--
Jeff Garrett
Maintenance Tech III
Milgard Tempering


John Spencer MVP said:
Part of the problem is that you have a field named Date. Date is a reserved
word in Access. Date can mean the current system date and therefore things
can get confused with that.

This is fairly straightforward and should work.

Parameters [Enter Start Date] as DateTime
, [Enter End Date] as DateTime;
SELECT [Project Time].[Date]
, [Project Time].[Time Start]
, [Project Time].Technition
, [Project Time].[Working On]
, [Project Time].[Time Stop]
, [Project Time].[Minutes Worked On Project]
, [Project Time].Department
FROM [Project Time]
WHERE [Project Time].[Date] Between
DateSerial(Year([Enter Start Date]), Month([Enter Start Date]),1) and
DateSerial(Year([Enter End Date]), 1 + Month([Enter End Date]),0)

Sometimes copy and paste from the post will introduce extraneous characters
that cause problems.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Well I just gave it a try and it doesn’t work......Hmmmm I copied and
pasted the code and an error box popped up saying that there was a (dot) ! or
prentices in the wrong spot......This must be why I never learned code talk!
:) Back to the drawing board........I do appreciate the help though.......
Click to expand...
Click to expand...
 
J

John Spencer MVP

Open a new query
Don't add any tables - just close the dialog
Select View: SQL from the menu

Enter the query string that was in my post

Parameters [Enter Start Date] as DateTime
, [Enter End Date] as DateTime;
SELECT [Project Time].[Date]
, [Project Time].[Time Start]
, [Project Time].Technition
, [Project Time].[Working On]
, [Project Time].[Time Stop]
, [Project Time].[Minutes Worked On Project]
, [Project Time].Department
FROM [Project Time]
WHERE [Project Time].[Date] Between
DateSerial(Year([Enter Start Date]), Month([Enter Start Date]),1) and
DateSerial(Year([Enter End Date]), 1 + Month([Enter End Date]),0)

Try running the query. If it works, great! If it doesn't work, post back
with the error message or the problem.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
G

garrett901

I think I got it.....Thanks..........
--
Jeff Garrett
Maintenance Tech III
Milgard Tempering


John Spencer MVP said:
Open a new query
Don't add any tables - just close the dialog
Select View: SQL from the menu

Enter the query string that was in my post

Parameters [Enter Start Date] as DateTime
, [Enter End Date] as DateTime;
SELECT [Project Time].[Date]
, [Project Time].[Time Start]
, [Project Time].Technition
, [Project Time].[Working On]
, [Project Time].[Time Stop]
, [Project Time].[Minutes Worked On Project]
, [Project Time].Department
FROM [Project Time]
WHERE [Project Time].[Date] Between
DateSerial(Year([Enter Start Date]), Month([Enter Start Date]),1) and
DateSerial(Year([Enter End Date]), 1 + Month([Enter End Date]),0)

Try running the query. If it works, great! If it doesn't work, post back
with the error message or the problem.

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

Thanks for your help.......I haven’t been ignoring the advice I just
haven’t had the time this past week to devote to the project.......Please
forgive my lack of "code" understanding. The code after your sentence, "This
is fairly straightforward and should work." Where would I insert that? My
manipulation of the ACCESS program is fairly limited and mostly don on a
trial and error basis. I would suspect that if an expert like yourself were
to look at my entire database program that I am using (it works quiet well
for us) you would probably think something like, "What the He-Double
toothpicks is this!!!†Once again, thanks for your help...JWG
Click to expand...
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

Returning zero in a query? 3
End of Month: Parameter Query? 3
Query By Month and Year - Access 2007 2
Query by Month and Year (Repost) 2
Modifying my Date Select Query 2
Nested IIF statements 2
current month / year WHERE 8
Fiscal Year Query 1

Top