Parameter Query?

[Hank]

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

 

[Bob Barrows [MVP]]

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?

 

[Hank]

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

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"

 

[Bob Barrows [MVP]]

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:]

 

[Hank]

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");

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"

 

[Bob Barrows [MVP]]

SQL View:
PARAMETERS [Month] Short;

This is wrong. It should say:

PARAMETERS [Enter Month] Text (255);

 

[Hank]

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?

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"

 

[Bob Barrows [MVP]]

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.

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?

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"

 

[Bob Barrows [MVP]]

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.

 

[Bob Barrows [MVP]]

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?

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:]))

 

[Hank]

Thanks for all your help Bob.

I deleted my query and started over with this:
SELECT Birthdaysqry.KnownBy, Birthdaysqry.LastName, Birthdaysqry.Birthdate,
Birthdaysqry.Month, Birthdaysqry.Day
FROM Birthdaysqry
WHERE (((Birthdaysqry.Month)=[Enter two digit month:]));

Could not get it to work when I put PARAMETERS at the top. I Have [Enter
two digit month:] as Criteria. And it works!
Might be some things I am missing that will make it work better. I like the
idea of only having to enter a single digit month but for now it's OK.
Thanks again
Hank

Thanks again.

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?

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"