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!

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



"Sara" <(E-Mail Removed)> wrote in message
news:02C7C29D-4661-451E-BA80-(E-Mail Removed)...
> 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!

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sara" <(E-Mail Removed)> wrote in message
news:02C7C29D-4661-451E-BA80-(E-Mail Removed)...
> 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:

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



"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Sara" <(E-Mail Removed)> wrote in message
> news:02C7C29D-4661-451E-BA80-(E-Mail Removed)...
>> 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!
>
>

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).

"Rick B" wrote:

> 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
>
>
>
> "Sara" <(E-Mail Removed)> wrote in message
> news:02C7C29D-4661-451E-BA80-(E-Mail Removed)...
> > 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!
>
>
>

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" wrote:

> 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!

That worked PERFECTLY! Thank you so much.

"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Sara" <(E-Mail Removed)> wrote in message
> news:02C7C29D-4661-451E-BA80-(E-Mail Removed)...
> > 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!
>
>
>

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rick B" <Anonymous> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> "Sara" <(E-Mail Removed)> wrote in message
>> news:02C7C29D-4661-451E-BA80-(E-Mail Removed)...
>>> 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.