How do I write a Criteria to search by month??

[Joe Leon]

In a table, along with other information in the record I have a field with
dates (short date) ... below I'm showing the column of three records.

11/01/07
11/10/06
01/11/08

I want to build a query to search by month so that when prompted, I enter
11, then I want the 11/01/07 and 11/10/06 to appear but not the 01/11/08.



What is the best way to write the criteria in the query so that is searches
by the number of the month?

I tried: Like [Enter Month] & DateSerial(Year(Date())+1,1,1)
but it's not working at all. All I get is the prompt but when I enter 11 no
records show.

THANKS! ... Joe....

 

[fredg]

In a table, along with other information in the record I have a field with
dates (short date) ... below I'm showing the column of three records.

11/01/07
11/10/06
01/11/08

I want to build a query to search by month so that when prompted, I enter
11, then I want the 11/01/07 and 11/10/06 to appear but not the 01/11/08.

What is the best way to write the criteria in the query so that is searches
by the number of the month?

I tried: Like [Enter Month] & DateSerial(Year(Date())+1,1,1)
but it's not working at all. All I get is the prompt but when I enter 11 no
records show.

THANKS! ... Joe....

Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]

 

[Joe Leon]

Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]

Are you saying to make a dedicated column for the month?
Thanks,
Joe...

In a table, along with other information in the record I have a field with
dates (short date) ... below I'm showing the column of three records.

11/01/07
11/10/06
01/11/08

I want to build a query to search by month so that when prompted, I enter
11, then I want the 11/01/07 and 11/10/06 to appear but not the 01/11/08.

What is the best way to write the criteria in the query so that is searches
by the number of the month?

I tried: Like [Enter Month] & DateSerial(Year(Date())+1,1,1)
but it's not working at all. All I get is the prompt but when I enter 11 no
records show.

THANKS! ... Joe....

Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]

 

[Ron2006]

Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]

Are you saying to make a dedicated column for the month?
Thanks,
Joe...

In a table, along with other information in the record I have a field with
dates (short date) ... below I'm showing the column of three records.
11/01/07
11/10/06
01/11/08
I want to build a query to search by month so that when prompted, I enter
11, then I want the 11/01/07 and 11/10/06 to appear but not the 01/11/08.
What is the best way to write the criteria in the query so that is searches
by the number of the month?
I tried: Like [Enter Month] & DateSerial(Year(Date())+1,1,1)
but it's not working at all. All I get is the prompt but when I enter 11 no
records show.
THANKS! ... Joe....

Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]

- Show quoted text -

The column that Fred is creating is a "temporary" field that is
created dynamically and exist ONLY in the query.

If you use the query builder
1) select all the fields from the table that you want to see in the
query.
2) go to the next open column and enter what Fred suggested.
TheMonth:Month([DateFieldname])
3) As the criteria for the column enter
[Enter Month Number]

When you run the query you will see all the columns you have selected
from the table AND you will see a column labelled "TheMonth"

Queries can include all sorts of calculated/partial fields in that
same manner.

Ron

 

[Joe Leon]

Thank you Fred and Ron ! It works great!

Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]

Are you saying to make a dedicated column for the month?
Thanks,
Joe...

In a table, along with other information in the record I have a field with
dates (short date) ... below I'm showing the column of three records.

I want to build a query to search by month so that when prompted, I enter
11, then I want the 11/01/07 and 11/10/06 to appear but not the 01/11/08.

What is the best way to write the criteria in the query so that is searches
by the number of the month?

I tried: Like [Enter Month] & DateSerial(Year(Date())+1,1,1)
but it's not working at all. All I get is the prompt but when I enter 11 no
records show.

THANKS! ... Joe....

Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]

- Show quoted text -

The column that Fred is creating is a "temporary" field that is
created dynamically and exist ONLY in the query.

If you use the query builder
1) select all the fields from the table that you want to see in the
query.
2) go to the next open column and enter what Fred suggested.
TheMonth:Month([DateFieldname])
3) As the criteria for the column enter
[Enter Month Number]

When you run the query you will see all the columns you have selected
from the table AND you will see a column labelled "TheMonth"

Queries can include all sorts of calculated/partial fields in that
same manner.

Ron

 

[Ron2006]

If you end up having more than one year's information in that table,
you may want to add the year to that query in much the same manner. or
combined.

Ron