Birthday Query by Month

[Sara]

I've got a database of clients and I want to be able to search for all the
employees born during a particular month. The field name of the birthday is
[Date of Birth] and it is in Date/Time format. So for example, when I run the
query, I want to be prompted to put in a number for the month (08 = August).
So when I put in 08, it will bring up all the employees born during August.
Everytime I run the query I want to be prompted to enter the numerical value
of the month, because this is for sending out birthday cards for employees
born during the upcoming month.

Thanks!

 

[Rick B]

I would add a new column to my query with the following...

BirthMonth: Month([Date of Birth]

in the criteria for that column, I'd put...

=[Enter Month to Print 01=Jan, 02=Feb, etc.]


Then I'd "uncheck" that column so it would be used to select records, but
would not show the column in my query results.

 

[Allen Browne]

1. Create a query, using this table.

2. In query design view, type this into a fresh column in the Field row:
BirthMonth: Month([Date Of Birth])

3. In the Criteria row under this field, enter:
[Which month number (1-12)?]

4. (Optional) Choose Parameters on the Query menu.
Access pops up a dialog.
Enter this row:
[Which month number (1-12)?] Integer

 

[Rick B]

Allen:

What does the "parameter" do in the query menu? I've never used it. Must
your entries there match exactly what you enter as criteria in your query
( I assume so)?

--
Rick B

1. Create a query, using this table.

2. In query design view, type this into a fresh column in the Field row:
BirthMonth: Month([Date Of Birth])

3. In the Criteria row under this field, enter:
[Which month number (1-12)?]

4. (Optional) Choose Parameters on the Query menu.
Access pops up a dialog.
Enter this row:
[Which month number (1-12)?] Integer

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've got a database of clients and I want to be able to search for all
the
employees born during a particular month. The field name of the birthday
is
[Date of Birth] and it is in Date/Time format. So for example, when I run
the
query, I want to be prompted to put in a number for the month (08 =
August).
So when I put in 08, it will bring up all the employees born during
August.
Everytime I run the query I want to be prompted to enter the numerical
value
of the month, because this is for sending out birthday cards for
employees
born during the upcoming month.

Thanks!

 

[Sara]

Thanks I'll try that....

I didn't realize I posted twice. My computer froze after the first post and
I didn't think it went through, so I did it again. (and once in the Query
forum - so just ignore that post too).

I would add a new column to my query with the following...

BirthMonth: Month([Date of Birth]

in the criteria for that column, I'd put...

=[Enter Month to Print 01=Jan, 02=Feb, etc.]


Then I'd "uncheck" that column so it would be used to select records, but
would not show the column in my query results.


--
Rick B

I've got a database of clients and I want to be able to search for all the
employees born during a particular month. The field name of the birthday
is
[Date of Birth] and it is in Date/Time format. So for example, when I run
the
query, I want to be prompted to put in a number for the month (08 =
August).
So when I put in 08, it will bring up all the employees born during
August.
Everytime I run the query I want to be prompted to enter the numerical
value
of the month, because this is for sending out birthday cards for employees
born during the upcoming month.

Thanks!

 

[KARL DEWEY]

Why not have the query to list next month’s birthdays without a prompt?

In query design view add a column like –
Birth Month: Format([Date of Birth], “mâ€)

This extracts the numerical month of the individual’s birth.

In the criteria row put –
Format(DateAdd(“mâ€,1,Date()),â€mâ€)

This adds one month to the current date and then has criteria for that
numerical month.

 

[Sara]

That worked PERFECTLY! Thank you so much.

1. Create a query, using this table.

2. In query design view, type this into a fresh column in the Field row:
BirthMonth: Month([Date Of Birth])

3. In the Criteria row under this field, enter:
[Which month number (1-12)?]

4. (Optional) Choose Parameters on the Query menu.
Access pops up a dialog.
Enter this row:
[Which month number (1-12)?] Integer

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've got a database of clients and I want to be able to search for all the
employees born during a particular month. The field name of the birthday
is
[Date of Birth] and it is in Date/Time format. So for example, when I run
the
query, I want to be prompted to put in a number for the month (08 =
August).
So when I put in 08, it will bring up all the employees born during
August.
Everytime I run the query I want to be prompted to enter the numerical
value
of the month, because this is for sending out birthday cards for employees
born during the upcoming month.

Thanks!

 

[Allen Browne]

A parameter is a value the user will supply at runtime.

If the query contains any name that JET cannot resolve, it assumes it is a
parameter, so it works even if you do not declare the parameter. The main
benefit of explicitly declaring it is so you can specify the data type.

If you declare a parameter as Integer (as in this example), Access accepts
numbers only, and the value is interpreted as a whole number, and then
matched as a whole number against the field. If the user supplies an invalid
value (bad date, out-of-range, ...), JET repeats the request until they
enter something sensible.

JET 4 is actually worse than previous versions when it comes to interpreting
the data type of fields - especially calculated fields. It needs all the
help it can get. Explicitly typecasting calculated fields and declaring
parameters are 2 ways you can help ensure that the entries are interpreted
and applied as you intended.

If your query includes a reference such as:
[Forms].[Form1].[Text0]
it might not be obvious that this actually a parameter. Explicitly declaring
the parameter (by typing exactly the same expression into the Parameters
dialog and specifying the type) again ensures that JET interprets the value
correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

What does the "parameter" do in the query menu? I've never used it. Must
your entries there match exactly what you enter as criteria in your query
( I assume so)?

--
Rick B

1. Create a query, using this table.

2. In query design view, type this into a fresh column in the Field row:
BirthMonth: Month([Date Of Birth])

3. In the Criteria row under this field, enter:
[Which month number (1-12)?]

4. (Optional) Choose Parameters on the Query menu.
Access pops up a dialog.
Enter this row:
[Which month number (1-12)?] Integer

I've got a database of clients and I want to be able to search for all
the
employees born during a particular month. The field name of the birthday
is
[Date of Birth] and it is in Date/Time format. So for example, when I
run the
query, I want to be prompted to put in a number for the month (08 =
August).
So when I put in 08, it will bring up all the employees born during
August.
Everytime I run the query I want to be prompted to enter the numerical
value
of the month, because this is for sending out birthday cards for
employees
born during the upcoming month.