Kelly said:I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
Marshall Barton said:Kelly said:I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
They adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Marshall Barton said:Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Marshall Barton said:[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.Marshall Barton said:Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Kelly said:I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
Marshall Barton said:[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
teveS said:Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
Marshall Barton said:[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Kelly said:Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS said:Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
SteveS said:Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS said:Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Kelly said:Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
SteveS said:Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
SteveS said:And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
SteveS said:Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Kelly said:SQL is not in my dummies book, so I have to ask - What is an SQL and where
would I find it to answer your question?
SteveS said:And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
:
Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
SteveS said:And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
SteveS said:Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
SteveS said:Open the query in design view
In the area where the table in shown, right click and select "SQL View"
Copy everything that is there: it should start with "Select...... "
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:SQL is not in my dummies book, so I have to ask - What is an SQL and where
would I find it to answer your question?
SteveS said:And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
:
Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Kelly said:Ok, I figured out the SQL - I think, is this it?
SELECT [copy of enrollment #'s].Date,
Nz([childcare])+Nz([Nursery])+Nz([Eastside]) AS [LT Total], [copy of
enrollment #'s].Childcare, [copy of enrollment #'s].Eastside, [copy of
enrollment #'s].Nursery, [copy of enrollment #'s].DCES, [copy of enrollment
#'s].MS, Nz([9th])+Nz([10th])+Nz([11th])+Nz([12th]) AS HS, [copy of
enrollment #'s].[9th], [copy of enrollment #'s].[10th], [copy of enrollment
#'s].[11th], [copy of enrollment #'s].[12th], [copy of enrollment
#'s].[Number of Classes], [copy of enrollment #'s].[Academic Support], [copy
of enrollment #'s].[Home Ed/ISP], [copy of enrollment #'s].[Gym - LT], [copy
of enrollment #'s].[Gym - DCS], [copy of enrollment #'s].Dance, [copy of
enrollment #'s].Cheer, [copy of enrollment #'s].[Gym Club], Nz([Gym -
LT])+Nz([Gym - DCS])+Nz([Dance])+Nz([Cheer])+Nz([Gym Club]) AS Enrichment,
[copy of enrollment #'s].[Peach Factory], Nz([LT
Total])+Nz([DCES])+Nz([MS])+Nz([HS])+Nz([Home Ed/ISP]) AS [DCS Total]
FROM [copy of enrollment #'s]
WHERE ((([copy of enrollment #'s].Date)>=#9/1/2000# And ([copy of enrollment
#'s].Date)<=#9/1/2010#))
ORDER BY [copy of enrollment #'s].Date, [copy of enrollment #'s].Month,
[copy of enrollment #'s].Year;
SteveS said:And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
:
Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Kelly said:I hope it's not too late, ignore the last SQL. It has a couple of fields
that may confuse you, I cleaned it up, and this is the real one. (I had some
old date fields that I am no using.)
SELECT Enrollment.Date, Nz([childcare])+Nz([Nursery])+Nz([Eastside]) AS [LT
Total], Enrollment.Childcare, Enrollment.Eastside, Enrollment.Nursery,
Enrollment.DCES, Enrollment.MS, Nz([9th])+Nz([10th])+Nz([11th])+Nz([12th]) AS
HS, Enrollment.[9th], Enrollment.[10th], Enrollment.[11th],
Enrollment.[12th], Enrollment.[Number of Classes], Enrollment.[Academic
Support], Enrollment.[Home Ed/ISP], Enrollment.[Gym - LT], Enrollment.[Gym -
DCS], Enrollment.Dance, Enrollment.Cheer, Enrollment.[Gym Club], Nz([Gym -
LT])+Nz([Gym - DCS])+Nz([Dance])+Nz([Cheer])+Nz([Gym Club]) AS Enrichment,
Enrollment.[Peach Factory], Nz([LT
Total])+Nz([DCES])+Nz([MS])+Nz([HS])+Nz([Home Ed/ISP]) AS [DCS Total],
Format([date],"mm") AS expr
FROM Enrollment
WHERE (((Enrollment.Date)>=#9/1/2000# And (Enrollment.Date)<=#9/1/2010#) AND
((Format([date],"mm"))=Format([Enter month (1 - 12)],"mm")))
ORDER BY Enrollment.Date;
Kelly said:Ok, I figured out the SQL - I think, is this it?
SELECT [copy of enrollment #'s].Date,
Nz([childcare])+Nz([Nursery])+Nz([Eastside]) AS [LT Total], [copy of
enrollment #'s].Childcare, [copy of enrollment #'s].Eastside, [copy of
enrollment #'s].Nursery, [copy of enrollment #'s].DCES, [copy of enrollment
#'s].MS, Nz([9th])+Nz([10th])+Nz([11th])+Nz([12th]) AS HS, [copy of
enrollment #'s].[9th], [copy of enrollment #'s].[10th], [copy of enrollment
#'s].[11th], [copy of enrollment #'s].[12th], [copy of enrollment
#'s].[Number of Classes], [copy of enrollment #'s].[Academic Support], [copy
of enrollment #'s].[Home Ed/ISP], [copy of enrollment #'s].[Gym - LT], [copy
of enrollment #'s].[Gym - DCS], [copy of enrollment #'s].Dance, [copy of
enrollment #'s].Cheer, [copy of enrollment #'s].[Gym Club], Nz([Gym -
LT])+Nz([Gym - DCS])+Nz([Dance])+Nz([Cheer])+Nz([Gym Club]) AS Enrichment,
[copy of enrollment #'s].[Peach Factory], Nz([LT
Total])+Nz([DCES])+Nz([MS])+Nz([HS])+Nz([Home Ed/ISP]) AS [DCS Total]
FROM [copy of enrollment #'s]
WHERE ((([copy of enrollment #'s].Date)>=#9/1/2000# And ([copy of enrollment
#'s].Date)<=#9/1/2010#))
ORDER BY [copy of enrollment #'s].Date, [copy of enrollment #'s].Month,
[copy of enrollment #'s].Year;
SteveS said:And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
:
Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
SteveS said:Kelly,
OK, make these changes:
Change the calculated query column to:
expr: Month(datefield)
and change the criteria to.
[Enter month (1 - 12)]
You can change "exp:" to "MthOfDate:" or whatever you want to name it.
---------------------------------------------
NOTE: Date is a reserved word in Access and shouldn't be used as an object
name. Also, it is not descriptive of the field. "Date" of what? Enrollment?
Birthday? Graduated?
Using spaces, punctuation (') or special characters (#) in object names
(field, table, query) generally causes headaches. It is just as easy to read
NumberOfClasses as [Number of Classes] and it is easier to use in queries,
forms and reports.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:I hope it's not too late, ignore the last SQL. It has a couple of fields
that may confuse you, I cleaned it up, and this is the real one. (I had some
old date fields that I am no using.)
SELECT Enrollment.Date, Nz([childcare])+Nz([Nursery])+Nz([Eastside]) AS [LT
Total], Enrollment.Childcare, Enrollment.Eastside, Enrollment.Nursery,
Enrollment.DCES, Enrollment.MS, Nz([9th])+Nz([10th])+Nz([11th])+Nz([12th]) AS
HS, Enrollment.[9th], Enrollment.[10th], Enrollment.[11th],
Enrollment.[12th], Enrollment.[Number of Classes], Enrollment.[Academic
Support], Enrollment.[Home Ed/ISP], Enrollment.[Gym - LT], Enrollment.[Gym -
DCS], Enrollment.Dance, Enrollment.Cheer, Enrollment.[Gym Club], Nz([Gym -
LT])+Nz([Gym - DCS])+Nz([Dance])+Nz([Cheer])+Nz([Gym Club]) AS Enrichment,
Enrollment.[Peach Factory], Nz([LT
Total])+Nz([DCES])+Nz([MS])+Nz([HS])+Nz([Home Ed/ISP]) AS [DCS Total],
Format([date],"mm") AS expr
FROM Enrollment
WHERE (((Enrollment.Date)>=#9/1/2000# And (Enrollment.Date)<=#9/1/2010#) AND
((Format([date],"mm"))=Format([Enter month (1 - 12)],"mm")))
ORDER BY Enrollment.Date;
Kelly said:Ok, I figured out the SQL - I think, is this it?
SELECT [copy of enrollment #'s].Date,
Nz([childcare])+Nz([Nursery])+Nz([Eastside]) AS [LT Total], [copy of
enrollment #'s].Childcare, [copy of enrollment #'s].Eastside, [copy of
enrollment #'s].Nursery, [copy of enrollment #'s].DCES, [copy of enrollment
#'s].MS, Nz([9th])+Nz([10th])+Nz([11th])+Nz([12th]) AS HS, [copy of
enrollment #'s].[9th], [copy of enrollment #'s].[10th], [copy of enrollment
#'s].[11th], [copy of enrollment #'s].[12th], [copy of enrollment
#'s].[Number of Classes], [copy of enrollment #'s].[Academic Support], [copy
of enrollment #'s].[Home Ed/ISP], [copy of enrollment #'s].[Gym - LT], [copy
of enrollment #'s].[Gym - DCS], [copy of enrollment #'s].Dance, [copy of
enrollment #'s].Cheer, [copy of enrollment #'s].[Gym Club], Nz([Gym -
LT])+Nz([Gym - DCS])+Nz([Dance])+Nz([Cheer])+Nz([Gym Club]) AS Enrichment,
[copy of enrollment #'s].[Peach Factory], Nz([LT
Total])+Nz([DCES])+Nz([MS])+Nz([HS])+Nz([Home Ed/ISP]) AS [DCS Total]
FROM [copy of enrollment #'s]
WHERE ((([copy of enrollment #'s].Date)>=#9/1/2000# And ([copy of enrollment
#'s].Date)<=#9/1/2010#))
ORDER BY [copy of enrollment #'s].Date, [copy of enrollment #'s].Month,
[copy of enrollment #'s].Year;
:
And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
:
Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
Kelly said:Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
Kelly said:It worked - this is awesome. Thank you so much! I am thankful for you guys,
I wish our school could hire someone to do all this progamming for me, but we
are a small non-profit with no extra in our budget. This board is a life
saver.
Can you suggest a book other than my Dummies book that is good to refer to?
SteveS said:Kelly,
OK, make these changes:
Change the calculated query column to:
expr: Month(datefield)
and change the criteria to.
[Enter month (1 - 12)]
You can change "exp:" to "MthOfDate:" or whatever you want to name it.
---------------------------------------------
NOTE: Date is a reserved word in Access and shouldn't be used as an object
name. Also, it is not descriptive of the field. "Date" of what? Enrollment?
Birthday? Graduated?
Using spaces, punctuation (') or special characters (#) in object names
(field, table, query) generally causes headaches. It is just as easy to read
NumberOfClasses as [Number of Classes] and it is easier to use in queries,
forms and reports.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Kelly said:I hope it's not too late, ignore the last SQL. It has a couple of fields
that may confuse you, I cleaned it up, and this is the real one. (I had some
old date fields that I am no using.)
SELECT Enrollment.Date, Nz([childcare])+Nz([Nursery])+Nz([Eastside]) AS [LT
Total], Enrollment.Childcare, Enrollment.Eastside, Enrollment.Nursery,
Enrollment.DCES, Enrollment.MS, Nz([9th])+Nz([10th])+Nz([11th])+Nz([12th]) AS
HS, Enrollment.[9th], Enrollment.[10th], Enrollment.[11th],
Enrollment.[12th], Enrollment.[Number of Classes], Enrollment.[Academic
Support], Enrollment.[Home Ed/ISP], Enrollment.[Gym - LT], Enrollment.[Gym -
DCS], Enrollment.Dance, Enrollment.Cheer, Enrollment.[Gym Club], Nz([Gym -
LT])+Nz([Gym - DCS])+Nz([Dance])+Nz([Cheer])+Nz([Gym Club]) AS Enrichment,
Enrollment.[Peach Factory], Nz([LT
Total])+Nz([DCES])+Nz([MS])+Nz([HS])+Nz([Home Ed/ISP]) AS [DCS Total],
Format([date],"mm") AS expr
FROM Enrollment
WHERE (((Enrollment.Date)>=#9/1/2000# And (Enrollment.Date)<=#9/1/2010#) AND
((Format([date],"mm"))=Format([Enter month (1 - 12)],"mm")))
ORDER BY Enrollment.Date;
:
Ok, I figured out the SQL - I think, is this it?
SELECT [copy of enrollment #'s].Date,
Nz([childcare])+Nz([Nursery])+Nz([Eastside]) AS [LT Total], [copy of
enrollment #'s].Childcare, [copy of enrollment #'s].Eastside, [copy of
enrollment #'s].Nursery, [copy of enrollment #'s].DCES, [copy of enrollment
#'s].MS, Nz([9th])+Nz([10th])+Nz([11th])+Nz([12th]) AS HS, [copy of
enrollment #'s].[9th], [copy of enrollment #'s].[10th], [copy of enrollment
#'s].[11th], [copy of enrollment #'s].[12th], [copy of enrollment
#'s].[Number of Classes], [copy of enrollment #'s].[Academic Support], [copy
of enrollment #'s].[Home Ed/ISP], [copy of enrollment #'s].[Gym - LT], [copy
of enrollment #'s].[Gym - DCS], [copy of enrollment #'s].Dance, [copy of
enrollment #'s].Cheer, [copy of enrollment #'s].[Gym Club], Nz([Gym -
LT])+Nz([Gym - DCS])+Nz([Dance])+Nz([Cheer])+Nz([Gym Club]) AS Enrichment,
[copy of enrollment #'s].[Peach Factory], Nz([LT
Total])+Nz([DCES])+Nz([MS])+Nz([HS])+Nz([Home Ed/ISP]) AS [DCS Total]
FROM [copy of enrollment #'s]
WHERE ((([copy of enrollment #'s].Date)>=#9/1/2000# And ([copy of enrollment
#'s].Date)<=#9/1/2010#))
ORDER BY [copy of enrollment #'s].Date, [copy of enrollment #'s].Month,
[copy of enrollment #'s].Year;
:
And the SQL of the query is......??
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.
:
Does a prompt pop up where you can enter a number for the month you want?
Would you post the SQL of the query?
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.
I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.
teveS" wrote:
Change the calculated query column to:
expr: Format(datefield, "mm")
and change the criteria to.
Format([Enter month (1 - 12)], "mm")
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.
9/2000
9/2001
9/2002
9/2003
9/2004
9/2005
I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.
:
[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)
The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.
Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!
I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.
Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.
:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.