Query for Month

[Stan]

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

[Rick Brandt]

Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Assuming the year you want is always the current year, use a criteria of...

>=DateSerial(Year(Date(), [Enter Month Number], 1)

AND <DateSerial(Year(Date(), [Enter Month Number]+1, 1)

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[fredg]

On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Stan]

I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??
On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004
On OK I am Prompted for the Month.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

Thanks to you all,

Stan Hanna
fredg wrote:

On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[lyle fairfield]

Stan wrote:

I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??
On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004
On OK I am Prompted for the Month.

Dates are stored and used as a special eight byte data type, peculiar to
Access, Jet and VB. They are not strings.
SQL, on the other hand is exclusively string.

Access may be able to interpret some strings as dates. Some of that
ability will depend upon Windows local settings.

SQL generally requires date strings to be in the format
#mm/dd/yyyy#
or
#yyyy-mm-dd#
to be understood by it.

All of this makes Dates and SQL very gnarly, and occasionally what works
on one computer, does not work on another.

I recommend simplicity, eg:

SELECT Orders.*
FROM Orders
WHERE
Month([OrderDate])=[Enter Month as Number eg 6]
AND
(Year([OrderDate])=[Enter Year as Number eg 1995 (default 1995)]
OR
([Enter Year as Number eg 1995 (default 1995)] Is Null
AND
Year([OrderDate]) = 1995))

This query works satisfactorily in Northwind. Note the last few lines
(after AND) which allow the user to skip entering a value for year but
just to click OK and to have year default to 1995.

[fredg]

On 23 Dec 2006 14:08:43 -0800, Stan wrote:

I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??
On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004
On OK I am Prompted for the Month.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

Thanks to you all,

Stan Hanna

fredg wrote:

>On 23 Dec 2006 11:02:11 -0800, Stan wrote:

>>I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

First create your query without any criteria.
Then while still in Design View, display the query SQL (by selecting
SQL from the View tool button).

Let's assume your query SQL looks a bit like this:
Select YourTable.[FieldName1], YourTable.[DateField] From YourTable;

Add the following (after first deleting the semi-colon) to the SQL:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year];

Run the query.
When prompted, enter a 2 digit month and a 4 digit year separated by a
slash /, i.e. 02/2004.

Then go back to Design View and take a look at the query grid. Access
has added a new column:
Format([DateField],"mm/yyyy")
and has unchecked the show check box, as the column is used just for
criteria.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Stan]

Fred,
Boy, I have got to look really stupid on this one. I followed your
instructions,created a new query, ran the query, prompted twice for
DateField and Month/Year, entering 02/2004. ( I am starting out with a
small DB but I have four entries in the month of 02/2004 ) When back
to the Design View and Access had added the new column as you
predicted. When I now run this query I bring up all of the DB records.
I have tried 02/2004 and 08/2004. Any ideas?

I appreciate your help on this project; It is for a worthy cause, a
local food pantry and my own education. I am retired, have worked in
technical fields for over 50 years and it really bugs me when I can
understand what a program is doing. What do I need to study to
understand the development and underlying actions of expressions?

Stan Hanna

fredg wrote:

On 23 Dec 2006 14:08:43 -0800, Stan wrote:

I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??
On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004
On OK I am Prompted for the Month.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

Thanks to you all,

Stan Hanna

fredg wrote:

On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

First create your query without any criteria.
Then while still in Design View, display the query SQL (by selecting
SQL from the View tool button).

Let's assume your query SQL looks a bit like this:
Select YourTable.[FieldName1], YourTable.[DateField] From YourTable;

Add the following (after first deleting the semi-colon) to the SQL:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year];

Run the query.
When prompted, enter a 2 digit month and a 4 digit year separated by a
slash /, i.e. 02/2004.

Then go back to Design View and take a look at the query grid. Access
has added a new column:
Format([DateField],"mm/yyyy")
and has unchecked the show check box, as the column is used just for
criteria.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Larry Linson]

Perhaps someone could help me understand why it isn't easy to include two
Fields in the Query Builder, as Month([nameofyourdatefield]) with Criteria
of [Enter month number] and Year([nameofyourdatefield]) with Criteria of
[Enter year]? Seems that would return the data for the specified month and
year.

Depending on the circumstances and the users, I might prefer to create a
form into which the user could select month and date from List or Combo
Boxes, and write VBA code to construct the SQL. And, if it's users executing
a Query, manual exception handling ... e.g., what happens when year isn't
entered... ought to suffice.

Larry Linson
Microsoft Access MVP
"Stan" <st*******@hotmail.comwrote in message
news:11*********************@48g2000cwx.googlegrou ps.com...

>

Fred,
Boy, I have got to look really stupid on this one. I followed your
instructions,created a new query, ran the query, prompted twice for
DateField and Month/Year, entering 02/2004. ( I am starting out with a
small DB but I have four entries in the month of 02/2004 ) When back
to the Design View and Access had added the new column as you
predicted. When I now run this query I bring up all of the DB records.
I have tried 02/2004 and 08/2004. Any ideas?

I appreciate your help on this project; It is for a worthy cause, a
local food pantry and my own education. I am retired, have worked in
technical fields for over 50 years and it really bugs me when I can
understand what a program is doing. What do I need to study to
understand the development and underlying actions of expressions?

Stan Hanna

fredg wrote:

>On 23 Dec 2006 14:08:43 -0800, Stan wrote:

I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??
On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004
On OK I am Prompted for the Month.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

Thanks to you all,

Stan Hanna

fredg wrote:
On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

First create your query without any criteria.
Then while still in Design View, display the query SQL (by selecting
SQL from the View tool button).

Let's assume your query SQL looks a bit like this:
Select YourTable.[FieldName1], YourTable.[DateField] From YourTable;

Add the following (after first deleting the semi-colon) to the SQL:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year];

Run the query.
When prompted, enter a 2 digit month and a 4 digit year separated by a
slash /, i.e. 02/2004.

Then go back to Design View and take a look at the query grid. Access
has added a new column:
Format([DateField],"mm/yyyy")
and has unchecked the show check box, as the column is used just for
criteria.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[lyle fairfield]

Larry Linson wrote:

Perhaps someone could help me understand why it isn't easy to include two
Fields in the Query Builder, as Month([nameofyourdatefield]) with Criteria
of [Enter month number] and Year([nameofyourdatefield]) with Criteria of
[Enter year]? Seems that would return the data for the specified month and
year.

When you get it, Larry, could you send that explanation to me too? Just
copy it on a piece of paper and slide it down beside the Nanaimo Bars in
my holiday basket, please.

Depending on the circumstances and the users, I might prefer to create a
form into which the user could select month and date from List or Combo
Boxes, and write VBA code to construct the SQL. And, if it's users executing
a Query, manual exception handling ... e.g., what happens when year isn't
entered... ought to suffice.

You've been away too much lately, Larry. CDMA has a new purge mechanism
which filters out those old, quaint solutions that were sensible, and
both helpful and simple for the user.

Well, Merry Christmas, anyway!

[Larry Linson]

"lyle fairfield" <ly***********@aim.comwrote

. . . Just copy it on a piece of paper and slide it
down beside the Nanaimo Bars in my holiday
basket, please.

I'm sorry, Lyle, but what with the smoked jalapenos, rattlesnake steak, and
chocolate-covered scorpions, there just wasn't room for Nanaimo Bars.

. . . old, quaint solutions that were sensible, and
both helpful and simple for the user.

Speaking of old and quaint, what'd you think of the wizards in Access 2007
creating macros instead of VBA?

Larry

[Lyle Fairfield]

Larry Linson wrote:

"lyle fairfield" <ly***********@aim.comwrote

. . . Just copy it on a piece of paper and slide it
down beside the Nanaimo Bars in my holiday
basket, please.

I'm sorry, Lyle, but what with the smoked jalapenos, rattlesnake steak, and
chocolate-covered scorpions, there just wasn't room for Nanaimo Bars.

. . . old, quaint solutions that were sensible, and
both helpful and simple for the user.

Speaking of old and quaint, what'd you think of the wizards in Access 2007
creating macros instead of VBA?

Larry

I'm not so familar with this; I expect I've mentioned before that I
create a new db and import all my objects to it if I see a wizard in
the project area. But I can't say that I am surprised.

I am finding Access 2007 works well in direct proprotion to how well I
know how to instruct it.

But at this time I feel that, more so than previous versions, it was
designed as a user's db, and not a developer's platform. Well, it's
MS's and they can do whatever they want with it. Perhaps somewhere they
said in big bold letters, "Hey, you Access Deveopers, better move to
..Net cuz we're changing the beast a bit/lot with things like wizards
writing macros, etc, etc." If they did I missed it.

Perhaps, they will be widly successful. But here in CDMA we see (IMO)
more and more floundering amateurs who have accpted MS's invitation to
create the home workshop db and are now struggling to keep its head
above water.

[Stan]

To Larry and Lyle,
I expect your postings are of interest to some readers who are
at a higher level of development than I am and maybe someday in the
future I will will look back at them in enlightment, but right now I
don't see how they attack my problem.

Have a good holiday,
Stan Hanna

Larry Linson wrote:

"lyle fairfield" <ly***********@aim.comwrote

. . . Just copy it on a piece of paper and slide it
down beside the Nanaimo Bars in my holiday
basket, please.

I'm sorry, Lyle, but what with the smoked jalapenos, rattlesnake steak, and
chocolate-covered scorpions, there just wasn't room for Nanaimo Bars.

. . . old, quaint solutions that were sensible, and
both helpful and simple for the user.

Speaking of old and quaint, what'd you think of the wizards in Access 2007
creating macros instead of VBA?

Larry

[Lyle Fairfield]

Stan wrote:

To Larry and Lyle,
I expect your postings are of interest to some readers who are
at a higher level of development than I am and maybe someday in the
future I will will look back at them in enlightment, but right now I
don't see how they attack my problem.

I wish you continued success.

[David F Cox]

response within originalp post

"Stan" <st*******@hotmail.comwrote in message
news:11**********************@48g2000cwx.googlegro ups.com...

>I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??

How is Fred supposed to know the name of your date field?
All he can do is show you where to put the name of your field, which is
where he has put [datefield]

On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004

and that should give you the result for feb 2004

On OK I am Prompted for the Month.

This looks like you have [month] somewhere that you do not need it.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

No, because you have not shown us the query. All I can do is guess, which is
what I have done.

In future make your best attempt at getting the result you want. Go to SQL
view, Copy and paste the SQL statement that you have and post it to the
newsgroup. That way we can see exactly where you are going wrong, and we can
respond using the names of your fields and tables.

Thanks to you all,

Stan Hanna
fredg wrote:

>On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Lyle Fairfield]

fredg wrote:

Then go back to Design View and take a look at the query grid. Access
has added a new column:
Format([DateField],"mm/yyyy")
and has unchecked the show check box, as the column is used just for
criteria.

As I have mentioned in another (unwelcome it seems) part of this thread
dealing with Dates, SQL and Parameters is challenging.

On my home computer I find that when the Date Separator is set to "-"
in Windows Regional Settings then

Design View shows
Format([OrderDate],"mm-yyyy")

even though the SQL view shows
Format([OrderDate],"mm/yyyy")

and
the parameter must be input as
06-2006
and not as
06/2006
in order for the query to return the desired records.

I suppose that if this could happen on my computer it could happen
anywhere.

In any case, my contention that it's advisable to be as simple as
possible when entering date parameters is supported and two parameters
(one for month and one for year) which accept very simple entries such
as 6 and 2006 are likely to be successful consistently while mm/yyyy
may not.

[fredg]

On Sat, 23 Dec 2006 17:30:41 -0800, fredg wrote:

On 23 Dec 2006 14:08:43 -0800, Stan wrote:

>I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??
On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004
On OK I am Prompted for the Month.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

Thanks to you all,

Stan Hanna

fredg wrote:

>>On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

First create your query without any criteria.
Then while still in Design View, display the query SQL (by selecting
SQL from the View tool button).

Let's assume your query SQL looks a bit like this:
Select YourTable.[FieldName1], YourTable.[DateField] From YourTable;

Add the following (after first deleting the semi-colon) to the SQL:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year];

Run the query.
When prompted, enter a 2 digit month and a 4 digit year separated by a
slash /, i.e. 02/2004.

Then go back to Design View and take a look at the query grid. Access
has added a new column:
Format([DateField],"mm/yyyy")
and has unchecked the show check box, as the column is used just for
criteria.

My use of [DateField] is generic. I have no idea of what the actual
name of your date field in the query is.

Change [DateField] to whatever the actual name of your date field is.

Let's assume it's name is actually SalesDate, in which case write:
Format([SalesDate],"mm/yyyy")

Let's go back to the beginning and change the method just a bit.

Create a brand new query with NO criteria.
Run the query. It should return all the records.

Now, add a new column to the query grid.
On the top line (the Field line) of a new column, write:

Format([SalesDate],"mm/yyyy")

Remember to change my assumed [SalesDate] to whtever the actual name
of your date field is.

As criteria on this new column, write:
[Enter Month/Year]

Uncheck the Show check box.

Run the query. You should be prompted once to
"Enter Month/Year"

Enter 02/2004 and click OK.
Only records for Feb. 2004 will be returned.

Remember that yiou must use the slash 02/2006 when entering the
parameter.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Stan]

Thanks to Fred and David for pointing me in the direction of the SQL
view. I think this may be the door opening I needed to start to
understand the underlaying base of ACCESS. I though SQL was to
advanced for me.

I apologize if I am wasting the time of the group with my questions. I
have spent many hours trying to work out the problem from both the
program help menus and books I have purchased on the subject. I then
searched the past group postings to assure I am not duplicate a
previous posting. If you will look at the postings I have received it
appears to a lay person that even the experts don't agree on how to
formulate the query.

Thank you again,

Stan

David F Cox wrote:

response within originalp post

"Stan" <st*******@hotmail.comwrote in message
news:11**********************@48g2000cwx.googlegro ups.com...

I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??

How is Fred supposed to know the name of your date field?
All he can do is show you where to put the name of your field, which is
where he has put [datefield]

On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004

and that should give you the result for feb 2004

On OK I am Prompted for the Month.

This looks like you have [month] somewhere that you do not need it.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

No, because you have not shown us the query. All I can do is guess, which is
what I have done.

In future make your best attempt at getting the result you want. Go to SQL
view, Copy and paste the SQL statement that you have and post it to the
newsgroup. That way we can see exactly where you are going wrong, and we can
respond using the names of your fields and tables.

Thanks to you all,

Stan Hanna
fredg wrote:

On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Rick Brandt]

Stan wrote:

Thanks to Fred and David for pointing me in the direction of the SQL
view. I think this may be the door opening I needed to start to
understand the underlaying base of ACCESS. I though SQL was to
advanced for me.

I apologize if I am wasting the time of the group with my questions.
I have spent many hours trying to work out the problem from both the
program help menus and books I have purchased on the subject. I then
searched the past group postings to assure I am not duplicate a
previous posting. If you will look at the postings I have received it
appears to a lay person that even the experts don't agree on how to
formulate the query.

Serious developers would almost never use the self-prompting parameter type of
query exactly because of the difficulties you are seeing. If you create a form
for entering the parameters and then use those values in the query it solves all
of these problems because the form can provide a mechanism for entering the date
and for passing it to the query that eliminates all the ambiguities.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Larry Linson]

Stan,

Did my suggestion to use the Month and Year functions in your Query not turn
out to be simple, straightforward, and workable? It certainly seemed to me
to be simpler than some suggestions you'd received. If your answer is, "I
don't know," then review my post which Lyle quoted, to wit:

"Perhaps someone could help me understand why
it isn't easy to include two Fields in the Query
Builder, as Month([nameofyourdatefield]) with
Criteria of [Enter month number] and
Year([nameofyourdatefield]) with Criteria of
[Enter year]? Seems that would return the data
for the specified month and year."

It was not just an idle question, and I thought it was sufficiently
un-subtle that it would be received as a suggestion as well as a question.
If not, I am suggesting now that you try those two Calculated Fields, with
appropriate criteria.

FYI, lots of message threads in this, and other, newgroups have branches
that veer off-subject, and even off-topic, but that doesn't negate the value
of the branches that are on-subject and on-topic. I really didn't expect my
"aside" question to Lyle about macros in Wizards to be beneficial in
addressing your problem, but his response was interesting to me, and, I
suspect, informative to some other readers, even if my off-subject question
to him perhaps should have begun a new thread.

Larry Linson
Microsoft Access MVP
"Stan" <st*******@hotmail.comwrote in message
news:11**********************@48g2000cwx.googlegro ups.com...

To Larry and Lyle,
I expect your postings are of interest to some readers who are
at a higher level of development than I am and maybe someday in the
future I will will look back at them in enlightment, but right now I
don't see how they attack my problem.

Have a good holiday,
Stan Hanna

Larry Linson wrote:

>"lyle fairfield" <ly***********@aim.comwrote

> . . . Just copy it on a piece of paper and slide it
down beside the Nanaimo Bars in my holiday
basket, please.

I'm sorry, Lyle, but what with the smoked jalapenos, rattlesnake steak,
and
chocolate-covered scorpions, there just wasn't room for Nanaimo Bars.

> . . . old, quaint solutions that were sensible, and
both helpful and simple for the user.

Speaking of old and quaint, what'd you think of the wizards in Access
2007
creating macros instead of VBA?

Larry

[Larry Linson]

"Stan" <st*******@hotmail.comwrote

Thanks to Fred and David for pointing me in
the direction of the SQL view. I think this may
be the door opening I needed to start to
understand the underlaying base of ACCESS.
I though SQL was to advanced for me.

Everyone is entitled to his/her own opinion, and mine is this: "While in
your continued development, knowing some SQL can be extremely useful, it
just isn't needed to address the problem of selecting Records by the month
and year of a date contained in the Record."

Larry Linson
Microsoft Access MVP

[David F Cox]

"Stan" <st*******@hotmail.comwrote in message
news:11*********************@48g2000cwx.googlegrou ps.com...

Thanks to Fred and David for pointing me in the direction of the SQL
view. I think this may be the door opening I needed to start to
understand the underlaying base of ACCESS. I though SQL was to
advanced for me.

I apologize if I am wasting the time of the group with my questions. I
have spent many hours trying to work out the problem from both the
program help menus and books I have purchased on the subject. I then
searched the past group postings to assure I am not duplicate a
previous posting. If you will look at the postings I have received it
appears to a lay person that even the experts don't agree on how to
formulate the query.

Thank you again,

We would not be trying to answer questions if we thought it was a waste of
time.

The versatility of Access ensures that there are a multitude of ways of
performing most tasks. Some responses will point to the easiest, some to the
most efficient, and some to the most reliable. Unfortunately, every once in
a while a response will just be wrong. I do not recall seeing any "wrong"
responses to your question, just different people focussing on different
aspects of the problem, with something to learn from all of them.

Stan

David F Cox wrote:

>response within originalp post

"Stan" <st*******@hotmail.comwrote in message
news:11**********************@48g2000cwx.googlegr oups.com...

>I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??

How is Fred supposed to know the name of your date field?
All he can do is show you where to put the name of your field, which is
where he has put [datefield]

On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004

and that should give you the result for feb 2004

On OK I am Prompted for the Month.

This looks like you have [month] somewhere that you do not need it.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

No, because you have not shown us the query. All I can do is guess, which
is
what I have done.

In future make your best attempt at getting the result you want. Go to
SQL
view, Copy and paste the SQL statement that you have and post it to the
newsgroup. That way we can see exactly where you are going wrong, and we
can
respond using the names of your fields and tables.

Thanks to you all,

Stan Hanna
fredg wrote:
On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy.
I
need a Query that will prompt for the month, ie. 6 for June, and
will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Stan]

David,
I understand and agree with what you are saying. After reading
through them the postings have been most helpful. I was somewhat
frustrated become most of the suggestions did not work in my situation,
probable because I did not enter them correctly. But, I always learn
something from every encounter. With that information I have gone back
to some books I have on the subject and expect to work my way through
the problem.

Thanks again,
Stan

David F Cox wrote:

"Stan" <st*******@hotmail.comwrote in message
news:11*********************@48g2000cwx.googlegrou ps.com...

Thanks to Fred and David for pointing me in the direction of the SQL
view. I think this may be the door opening I needed to start to
understand the underlaying base of ACCESS. I though SQL was to
advanced for me.

I apologize if I am wasting the time of the group with my questions. I
have spent many hours trying to work out the problem from both the
program help menus and books I have purchased on the subject. I then
searched the past group postings to assure I am not duplicate a
previous posting. If you will look at the postings I have received it
appears to a lay person that even the experts don't agree on how to
formulate the query.

Thank you again,

We would not be trying to answer questions if we thought it was a waste of
time.

The versatility of Access ensures that there are a multitude of ways of
performing most tasks. Some responses will point to the easiest, some to the
most efficient, and some to the most reliable. Unfortunately, every once in
a while a response will just be wrong. I do not recall seeing any "wrong"
responses to your question, just different people focussing on different
aspects of the problem, with something to learn from all of them.

Stan

David F Cox wrote:

response within originalp post

"Stan" <st*******@hotmail.comwrote in message
news:11**********************@48g2000cwx.googlegro ups.com...
I decided to follow Freds thread since it looks the simplest. I must
learn a great more about building expressions.

Fred,
First I an assuming I type the expression in the Criteria line under
my date field.

I enter the expression:

Format([DateField],"mm/yyyy") = [Enter Month/Year]

On RUN I am prompted for the DateField Parameter Value
Should I enter the Field Name ??

How is Fred supposed to know the name of your date field?
All he can do is show you where to put the name of your field, which is
where he has put [datefield]
On OK I am prompted for the Month/Year Parameter Value
I enter 02/2004

and that should give you the result for feb 2004
On OK I am Prompted for the Month.


This looks like you have [month] somewhere that you do not need it.

I enter 02

On OK no records are returned.

Can you spot what I am doing wrong ?

No, because you have not shown us the query. All I can do is guess, which
is
what I have done.

In future make your best attempt at getting the result you want. Go to
SQL
view, Copy and paste the SQL statement that you have and post it to the
newsgroup. That way we can see exactly where you are going wrong, and we
can
respond using the names of your fields and tables.

Thanks to you all,

Stan Hanna
fredg wrote:
On 23 Dec 2006 11:02:11 -0800, Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy.
I
need a Query that will prompt for the month, ie. 6 for June, and
will
return all records in that month.

Just to help you understand Access dates, dates are not stored in any
particular "Format".
Dates are stored as the number of days since 12/30/1899.
Today's date is stored as 39074.
You can enter a date value as December 23, 2006, 12-23-2006,
12/23/2006, 23/12/2006, as well as 23 Dec. 2006, etc., and Access will
store 39074.

One way to return records for any particular month and year:

Where Format([DateField],"mm/yyyy") = [Enter Month/Year]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[CDMAPoster]

Stan wrote:

I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.

"The" Stan Hanna?

Visual Basic for Applications (VBA) and SQL give Access much of its
power and flexibility. Expressions in SQL add even more power and
flexibility to Access. Solving database problems in Access is an
excellent introduction to the world of SQL although it's good to be
aware that the style and syntax of SQL in Access is different than the
SQL used in, say, SQL Server. It takes time to get a feel for how
expressions can help and where they should be used so it's natural for
it to be unnatural.

I found out early that many of the books on Access cover general
principles. Consequently, the specifics needed to solve a particular
problem are often impossible to find in books. It's a little
disheartening to discover that a book with perhaps 700 pages or more
doesn't cover the specific information you need. You searched. You
made attempts. You earned the right to ask your question here. Plus,
you were polite. I believe that the posters in this thread were
genuinely trying to help and thought that the question was answered
before veering off topic. In fact, I respect all the posters who tried
to answer your question.

When I first started answering posts in this newsgroup there was a
particular poster who was exceptionally rude. Perhaps where they live
it's necessary to be rude and perhaps in their cultural context they
are a paragon of civility. I almost stopped posting because of that
perceived rudeness, but didn't. I'd like to think that this newsgroup
is a better place because of my contributions. So I say to you: Keep
at it. Post here as often as you'd like. Many competent Access
programmers are here who delight in helping people. We'll do our best
to answer your questions in what is hopefully a straightforward and
professional manner. If some posters are rude without cause, ignore
them!

There were some problems with the way the question was asked. Please
allow me to point these out. Pointing out the version of Access, the
operating system and that you are new to Access were good things.
Specifying that the query prompt for the month misdirected many from
good answers because most good Access programmers avoid query prompts,
usually as a way to protect users from themselves. The second problem
was that specifying a particular month may solve the problem for this
year's data but will cause problems once the second year's data is in
the database. The programmers in this newsgroup like to nip such
problems in the bud so they sought solutions to deal with this problem
by including the year information as well.

How would I do this? I would use a form to control the process of
picking a date.

One way is to use a calendar control on a form to ensure that a valid
date is chosen. Here's an example for two calendar controls called
ActiveXBegin and ActiveXEnd used to get a begin date and an end date:

http://groups.google.com/group/comp....64d683ede98f8c

A command button on the same form can run the query, or better yet a
report based on that query, that uses the Month and Year functions on
the value stored in the textbox to supply the query. Something like:

SELECT * FROM MyTable WHERE Month(MyDateField) =
Month(Forms!frmReportInput!txtMonthYear.Value) AND Year(MyDateField) =
Year(Forms!frmReportInput!txtMonthYear.Value);

Note that MyDateField is the name of the date field in your data table
used to determine which records are to be shown.

In actuality I would create the SQL string dynamically but this example
is close enough to see what is done. For reports that are opened from
forms I typically check to make sure that data is going to be returned
before opening the report and typically prevent the users from opening
the report directly from the database window.

Another way to choose the month is to put all the possible month/year
combinations for which records exist (determined from a query used to
fill it) into a combobox on the form. The query used to fill the
combobox can place the month and year values in separate columns that
can be ordered as desired. The query that the report is based on can
use something like Forms!frmReportInput!cbxMonthYear.Column(0) to get
the month number, etc. Again, a command button can be used to trigger
the creation of the report.

I hope this helps. I went into more detail than usual because of your
"newbie" status. Feel free to ask any questions here for the benefit
of all about anything I said. Maybe you'll be answering questions in
this newsgroup someday.

James A. Fortune
CD********@FortuneJames.com