Parameter Query?

I have a query in access 2003 with the following:

LastName
Birthdate - (example:. 11/01/1990)
Month: Format([Birthdate],"mm") --Expression to extract the month. In the
Criteria I have a parameter: "Enter Month:"
Day: Format([Birthdate],"dd") --Expression - to extract the day

I want is a list of birthdays for a given month.
I get no results when I run the query. I have tried entering "06", "6" and
"Jun" for month. Still no results. There are birthdays in the month I
entered.

What am I doing wrong?
Hank

Hank wrote:
I have a query in access 2003 with the following:

LastName
Birthdate - (example:. 11/01/1990)
Month: Format([Birthdate],"mm") --Expression to extract the month.
In the Criteria I have a parameter: "Enter Month:"
Day: Format([Birthdate],"dd") --Expression - to extract the day

I want is a list of birthdays for a given month.
I get no results when I run the query. I have tried entering "06",
"6" and "Jun" for month. Still no results. There are birthdays in
the month I entered.

What am I doing wrong?
Hank
What is the datatype of the birthdate field? is it a Date/Time or a Text
field?

If you remove the criteria and run the query, what does the output of that
Format function look like?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

The output of the query is:
Birthdaysqry Last Name Birthdate Month Day
Smith 3/13/1994 03 13
Cramer 11/29/1994 11 29
Long 1/29/1996 01 29
Likus 3/29/1996 03 29
Martin 4/23/1996 04 23
Johnes 5/29/1996 05 29
Applee 8/16/1996 08 16


The datatype from the table source is Date/time formatted to Short date
(mask 99/99/0000;0;_). The Month and Day field are Expressions from the
Birthdate field to sort the month order properly.
The query works fine without criteria. But I want a report of birthdays
for a given month.
While creating this table and query I tried formatting the birthdate as a
Medium Date (27-Jun-69), but I could only sort the months alphabetically.
Changing to Short Date allowed me to sort the months porperly.
Thanks

"Bob Barrows [MVP]" wrote in message
...
Hank wrote:
I have a query in access 2003 with the following:

LastName
Birthdate - (example:. 11/01/1990)
Month: Format([Birthdate],"mm") --Expression to extract the month.
In the Criteria I have a parameter: "Enter Month:"
Day: Format([Birthdate],"dd") --Expression - to extract the day

I want is a list of birthdays for a given month.
I get no results when I run the query. I have tried entering "06",
"6" and "Jun" for month. Still no results. There are birthdays in
the month I entered.

What am I doing wrong?
Hank
What is the datatype of the birthdate field? is it a Date/Time or a Text
field?

If you remove the criteria and run the query, what does the output of that
Format function look like?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hank wrote:
The output of the query is:
Birthdaysqry Last Name Birthdate Month Day
Smith 3/13/1994 03 13
Cramer 11/29/1994 11 29
Long 1/29/1996 01 29
Likus 3/29/1996 03 29
Martin 4/23/1996 04 23
Johnes 5/29/1996 05 29
Applee 8/16/1996 08 16


The datatype from the table source is Date/time formatted to Short
date (mask 99/99/0000;0;_). The Month and Day field are Expressions
from the Birthdate field to sort the month order properly.
The query works fine without criteria. But I want a report of
birthdays for a given month.

I know. I just wanted to see the output to give us a clue as to what
criterea to enter.

So entering "06" (without the quotes) doesn't work? I think I need to see
the sql statement if so. Switch the query to SQL View (View menu or toobar
button) and copy/paste the sql. It should look something like:

WHERE Format([Birthdate],"mm") = [Enter Month:]



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

SQL View:
PARAMETERS [Month] Short;
SELECT StudentsTbl.StudentID, StudentsTbl.KnownBy, StudentsTbl.LastName,
StudentsTbl.Birthdate, Format([Birthdate],"mm") AS [Month],
Format([Birthdate],"dd") AS [Day]
FROM StudentsTbl
WHERE (((Format([Birthdate],"mm"))="Enter Month:"))
ORDER BY StudentsTbl.Birthdate, Format([Birthdate],"mm");

"Bob Barrows [MVP]" wrote in message
...
Hank wrote:
The output of the query is:
Birthdaysqry Last Name Birthdate Month Day
Smith 3/13/1994 03 13
Cramer 11/29/1994 11 29
Long 1/29/1996 01 29
Likus 3/29/1996 03 29
Martin 4/23/1996 04 23
Johnes 5/29/1996 05 29
Applee 8/16/1996 08 16


The datatype from the table source is Date/time formatted to Short
date (mask 99/99/0000;0;_). The Month and Day field are Expressions
from the Birthdate field to sort the month order properly.
The query works fine without criteria. But I want a report of
birthdays for a given month.

I know. I just wanted to see the output to give us a clue as to what
criterea to enter.

So entering "06" (without the quotes) doesn't work? I think I need to see
the sql statement if so. Switch the query to SQL View (View menu or toobar
button) and copy/paste the sql. It should look something like:

WHERE Format([Birthdate],"mm") = [Enter Month:]



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hank wrote:
SQL View:
PARAMETERS [Month] Short;

This is wrong. It should say:

PARAMETERS [Enter Month] Text (255);


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Sorry, I don't understand.

I replaced "PARAMETERS[Month] Short;" , with "PARAMETERS[Enter Month] Text
(255);" in the SQL view but I still get blank results. ( I dont know what
Text(255); is telling me.)
I tried entering NOV and 11 for November - same results.
If I remove the Crieteria "Enter Month:" in the design view, I am still
prompted to enter a month. Then, no mater what month I enter (Nov or 11),
the results is every birthday in every month.
What did I do wrong?

"Bob Barrows [MVP]" wrote in message
...
Hank wrote:
SQL View:
PARAMETERS [Month] Short;

This is wrong. It should say:

PARAMETERS [Enter Month] Text (255);


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

The datatype of the parameter (whose name should be the same as the
prompting text) is Text, not numeric (Short).
You should be entering the month number when prompted, but single digit
months should be entered with the leading zero.

You are still getting prompted because you did not remove the parameter
definition when you removed the criterion.

As to what you are doing wrong ... I can't tell. I'm going to go try it
myself ... be right back.



Hank wrote:
Sorry, I don't understand.

I replaced "PARAMETERS[Month] Short;" , with "PARAMETERS[Enter Month]
Text (255);" in the SQL view but I still get blank results. ( I dont
know what Text(255); is telling me.)
I tried entering NOV and 11 for November - same results.
If I remove the Crieteria "Enter Month:" in the design view, I am
still prompted to enter a month. Then, no mater what month I enter
(Nov or 11), the results is every birthday in every month.
What did I do wrong?

"Bob Barrows [MVP]" wrote in message
...
Hank wrote:
SQL View:
PARAMETERS [Month] Short;

This is wrong. It should say:

PARAMETERS [Enter Month] Text (255);


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hank wrote:
Sorry, I don't understand.

I replaced "PARAMETERS[Month] Short;" , with "PARAMETERS[Enter Month]
Text (255);" in the SQL view but I still get blank results. ( I dont
know what Text(255); is telling me.)
I tried entering NOV and 11 for November - same results.
If I remove the Crieteria "Enter Month:" in the design view, I am
still prompted to enter a month. Then, no mater what month I enter
(Nov or 11), the results is every birthday in every month.
What did I do wrong?


Well, it seems to work for me. Here is my query:
PARAMETERS [Enter Month] Text ( 255 );
SELECT Table1.Field1, Table1.ID, Table1.VendorCtry, Table1.VendDate
FROM Table1
WHERE (((Format([VendDate],"mm"))=[Enter Month]));

When I enter 11, I get November dates. When I enter 06, I get June dates.

I don't know what else I can tell you.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hank wrote:
Sorry, I don't understand.

I replaced "PARAMETERS[Month] Short;" , with "PARAMETERS[Enter Month]
Text (255);" in the SQL view but I still get blank results. ( I dont
know what Text(255); is telling me.)
I tried entering NOV and 11 for November - same results.
If I remove the Crieteria "Enter Month:" in the design view, I am
still prompted to enter a month. Then, no mater what month I enter
(Nov or 11), the results is every birthday in every month.
What did I do wrong?

"Bob Barrows [MVP]" wrote in message
...
Hank wrote:
SQL View:
PARAMETERS [Month] Short;

This is wrong. It should say:

PARAMETERS [Enter Month] Text (255);



Instead of requiring your users to enter the leading zeroes, you might try
using the Month() function instead:

PARAMETERS [Enter Month] Short;
SELECT StudentsTbl.StudentID, StudentsTbl.KnownBy, StudentsTbl.LastName,
StudentsTbl.Birthdate,
Month([Birthdate]) AS [Month],
Format([Birthdate],"dd") AS [Day]
FROM StudentsTbl
WHERE Month([Birthdate])=[Enter Month:]
ORDER BY StudentsTbl.Birthdate, Format([Birthdate],"mm");

Now all the user has to enter for June dates is 6, instead of 06.

And I just saw what you were doing wrong: you had
WHERE (((Format([Birthdate],"mm"))="Enter Month:"))

And you should have had:
WHERE (((Format([Birthdate],"mm"))=[Enter Month:]))








--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"