Another Date Query

[Bill HR Specialist]

Okay I have a table with a list of birthdays mm/dd/yyyy in the query I want a parameter condition that asked for birthdays beween Months i.e give me all employees that have a birthday between January and March. I tried doing a DatePart conversion which brought me back just the Month as a number then used the Between parameter to bring me back betwen 1 and 3, but it also brings me back 11 12. I thought it should give me back exact match but it didn't.

 

[plog]

Sounds like your month field is a string. When comparing strings 11, 12, 123456789 all come between 1 and 3. Can you post your SQL?

 

[Bill HR Specialist]

Sounds like your month field is a string. When comparing strings 11, 12, 123456789 all come between 1 and 3. Can you post your SQL?

SELECT Employees.[Last Name], Employees.[Birth Date], Employees.Facility, DatePart('m',[Employees]![Birth Date]) AS Expr1
FROM Employees
WHERE (((Employees.[Birth Date]) Is Not Null) AND ((DatePart('m',[Employees]![Birth Date])) Between [Start Month as Number] And [End Month as Number]));

 

[plog]

Neat, I'm right but in an odd way. Ultimately this has to do with you allowing the user to input [Start Month as Number] and [End Month as Number]. Doing this makes the evaluation take place as string data, even though the field itself returns a numeric value.

You need to force the input values to be numeric, try this SQL:

SELECT Employees.[Last Name], Employees.[Birth Date], Employees.Facility, DatePart('m',[Employees]![Birth Date]) AS Expr1
FROM Employees
WHERE (((Employees.[Birth Date]) Is Not Null) AND ((DatePart('m',[Employees]![Birth Date])) Between Int([Start Month as Number]) And Int([End Month as Number])));

Putting Int() calls around the input data should work.

 

[Bill HR Specialist]

Since that was not going right found a better way, since my report need 3 months of Birthdays from current on out I used the Month() function instead in the condition: Between Month(Now()) And Month(Now())+2 and it returns what I need. Thanks to a little frustration I avoided needless input for a set report. Thanks