Setting Date Criteria

  • Thread starter Thread starter millerli26
  • Start date Start date
M

millerli26

I am trying to setup a query so that I will no longer need to copy and
paste into Excel to create the list I need. I have a table of customer
data from which I need to make a query for every month of the year so
that each query shows only customers that have birthdays in that month
with years ranging anywhere from 1940 or so to today. I am not very
experiencee in Access so I'm not sure what the proper criteria would be
to set this up. I was trying = "10/01/*", and Between "#10/01/*# And
#10/31/*#" Neither of those work because I guess you can't use
asterisks with dates. I'm hoping someone can tell what I am trying to
do from this though and let me know what I need to use.

Any and all help would be greatly appreciated.
 
I am trying to setup a query so that I will no longer need to copy and
paste into Excel to create the list I need. I have a table of customer
data from which I need to make a query for every month of the year so
that each query shows only customers that have birthdays in that month
with years ranging anywhere from 1940 or so to today. I am not very
experiencee in Access so I'm not sure what the proper criteria would be
to set this up. I was trying = "10/01/*", and Between "#10/01/*# And
#10/31/*#" Neither of those work because I guess you can't use
asterisks with dates. I'm hoping someone can tell what I am trying to
do from this though and let me know what I need to use.

Any and all help would be greatly appreciated.
Click to expand...

The year value is irrelevant, unless you also wish the person's age.

Where Month([DOB]) = [Enter the month number]

You will be prompted to enter the month number when the query is run.
Enter 10 to get all the birthdays in October.

If you wish to show all the birthdays for the entire year, then add a
new column to the query:
BirthMonth:Month([DOB])
Sort on this field.
In this case, no criteria is used.
 
When I try this it shows zero results when the query has run. Do I
first need to create a new field in the table that contains just
customer birth months? Currently the field we are using is titled
BirthDate with the format mm/dd/yyyy.

Thanks.
I am trying to setup a query so that I will no longer need to copy and
paste into Excel to create the list I need. I have a table of customer
data from which I need to make a query for every month of the year so
that each query shows only customers that have birthdays in that month
with years ranging anywhere from 1940 or so to today. I am not very
experiencee in Access so I'm not sure what the proper criteria would be
to set this up. I was trying = "10/01/*", and Between "#10/01/*# And
#10/31/*#" Neither of those work because I guess you can't use
asterisks with dates. I'm hoping someone can tell what I am trying to
do from this though and let me know what I need to use.

Any and all help would be greatly appreciated.
Click to expand...

The year value is irrelevant, unless you also wish the person's age.

Where Month([DOB]) = [Enter the month number]

You will be prompted to enter the month number when the query is run.
Enter 10 to get all the birthdays in October.

If you wish to show all the birthdays for the entire year, then add a
new column to the query:
BirthMonth:Month([DOB])
Sort on this field.
In this case, no criteria is used.
Click to expand...
 
When I try this it shows zero results when the query has run. Do I
first need to create a new field in the table that contains just
customer birth months? Currently the field we are using is titled
BirthDate with the format mm/dd/yyyy.

Thanks.
I am trying to setup a query so that I will no longer need to copy and
paste into Excel to create the list I need. I have a table of customer
data from which I need to make a query for every month of the year so
that each query shows only customers that have birthdays in that month
with years ranging anywhere from 1940 or so to today. I am not very
experiencee in Access so I'm not sure what the proper criteria would be
to set this up. I was trying = "10/01/*", and Between "#10/01/*# And
#10/31/*#" Neither of those work because I guess you can't use
asterisks with dates. I'm hoping someone can tell what I am trying to
do from this though and let me know what I need to use.

Any and all help would be greatly appreciated.
Click to expand...

The year value is irrelevant, unless you also wish the person's age.

Where Month([DOB]) = [Enter the month number]

You will be prompted to enter the month number when the query is run.
Enter 10 to get all the birthdays in October.

If you wish to show all the birthdays for the entire year, then add a
new column to the query:
BirthMonth:Month([DOB])
Sort on this field.
In this case, no criteria is used.
Click to expand...
Click to expand...

Where Month([DOB]) = [Enter the month number] is the SQL you will need
as the Where clause.

In Query Design View. click on View + SQL View.
Your query SQL will be displayed. Something like:

Select YourTableName.[CustomerName], YourTableName.[BirthDate] From
YourTableName;
Delete the last semi-colon.
Then write on the following line:

Where Month([BirthDate]) = [Enter the month number];

Run the query.
 
Excellent, it works, thank you very much :)
When I try this it shows zero results when the query has run. Do I
first need to create a new field in the table that contains just
customer birth months? Currently the field we are using is titled
BirthDate with the format mm/dd/yyyy.

Thanks.
On 23 Sep 2006 09:55:02 -0700, (e-mail address removed) wrote:

I am trying to setup a query so that I will no longer need to copy and
paste into Excel to create the list I need. I have a table of customer
data from which I need to make a query for every month of the year so
that each query shows only customers that have birthdays in that month
with years ranging anywhere from 1940 or so to today. I am not very
experiencee in Access so I'm not sure what the proper criteria would be
to set this up. I was trying = "10/01/*", and Between "#10/01/*# And
#10/31/*#" Neither of those work because I guess you can't use
asterisks with dates. I'm hoping someone can tell what I am trying to
do from this though and let me know what I need to use.

Any and all help would be greatly appreciated.

The year value is irrelevant, unless you also wish the person's age.

Where Month([DOB]) = [Enter the month number]

You will be prompted to enter the month number when the query is run.
Enter 10 to get all the birthdays in October.

If you wish to show all the birthdays for the entire year, then add a
new column to the query:
BirthMonth:Month([DOB])
Sort on this field.
In this case, no criteria is used.
Click to expand...
Click to expand...

Where Month([DOB]) = [Enter the month number] is the SQL you will need
as the Where clause.

In Query Design View. click on View + SQL View.
Your query SQL will be displayed. Something like:

Select YourTableName.[CustomerName], YourTableName.[BirthDate] From
YourTableName;
Delete the last semi-colon.
Then write on the following line:

Where Month([BirthDate]) = [Enter the month number];

Run the query.
Click to expand...
 
Create a calculated column defined as Month(Birthdate), then set the
criterion for that column to the number of the relevant month for each
selection. If some of your customers have birthdates before 1940, and
you want to exclude them, also put a criterion of '>= #1/1/1940#'
(omitting quotes) in the BirthDate column.


I am trying to setup a query so that I will no longer need to copy and
paste into Excel to create the list I need. I have a table of customer
data from which I need to make a query for every month of the year so
that each query shows only customers that have birthdays in that month
with years ranging anywhere from 1940 or so to today. I am not very
experiencee in Access so I'm not sure what the proper criteria would be
to set this up. I was trying = "10/01/*", and Between "#10/01/*# And
#10/31/*#" Neither of those work because I guess you can't use
asterisks with dates. I'm hoping someone can tell what I am trying to
do from this though and let me know what I need to use.

Any and all help would be greatly appreciated.
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top