Citeria Challenges

[ShannonMarie]

I have an employees table, and a months lookup table. I am attempting to create a query to run a report according to the month selected. I have set up a parameter box in the critera of the query. It is prompting me for the month I would like, however, it is still returning all records. This is the first report based on criteria that I have created. Any suggestions would be greatly appreciated. The query is to pull birthdays according to the birth month. As always, I am thankful for any input recieved.

 

[raskew]

You won't need a lookup table. Try copying/pasting this to a new query in Northwind.

SELECT Employees.LastName, Employees.FirstName, DatePart("m",[BirthDate]) AS Expr1, DatePart("d",[BirthDate]) AS Expr2, Trim([firstname])+" "+[lastname] AS Expr3, DateSerial(Year(Now()),([Expr1]),[Expr2]) AS Birthday
FROM Employees
WHERE (((DatePart("m",[BirthDate]))=[enter month (1-12)]))
ORDER BY DatePart("m",[BirthDate]), DatePart("d",[BirthDate]);

Run the query and, when prompted, enter 5 and you'll see all employees with a birthday in May.

Adapt the query to your particular situation and use it as the record source for your report.

Bob

 

[ShannonMarie]

I'm trying to work it

I have input the query you submitted in SQL view. I am being promted for "BirthDate" and then "Enter Month (1-12). I will fiddle with it. Thank you for you consideration and help. I will try to manipulate the results that I need. I have never worked with Northwind, but had some SQL last semester. Again, thank you Bob!

 

[ShannonMarie]

This did it!

SELECT Employees.EmpId, Employees.EmpLastName, Employees.EmpInitial, Employees.EmpFirstName, Employees.EmpDateOfBirth, Month([EmpDateOfBirth]) AS BirthMonth
FROM Employees
WHERE (((Month([EmpDateOfBirth]))=[Please Enter A Month (1-12)]));


Should anyone else need the same same it is here for reference.

Shannon