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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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....
 
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....
Click to expand...

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

As criteria for this column, write:
[Enter Month Number]
 
Hi said:
Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])

As criteria for this column, write:
[Enter Month Number]
Click to expand...

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

fredg said:
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....
Click to expand...

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

As criteria for this column, write:
[Enter Month Number]
Click to expand...
 
Hi said:
Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])
Click to expand...
As criteria for this column, write:
[Enter Month Number]
Click to expand...

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



fredg said:
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....
Click to expand...
Click to expand...
Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])
Click to expand...
As criteria for this column, write:
[Enter Month Number]
Click to expand...

- Show quoted text -
Click to expand...

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
 
Thank you Fred and Ron ! It works great!

Ron2006 said:
Hi said:
Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])
Click to expand...
As criteria for this column, write:
[Enter Month Number]
Click to expand...

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.
Click to expand...
What is the best way to write the criteria in the query so that is searches
by the number of the month?
Click to expand...
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.
Click to expand...
THANKS! ... Joe....
Click to expand...
Simply add a new column to the query grid.
TheMonth:Month([DateFieldname])
Click to expand...
As criteria for this column, write:
[Enter Month Number]
Click to expand...

- Show quoted text -
Click to expand...

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
Click to expand...
 
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
 
Back
Top