Date Range Query Only Returns Month and Day

[hammer187]

I have a query that prompts the user to input the start date and end date. the format of my database is mm/dd/yyyy. the format for my query is mm/dd/yyyy. It returns the correct month and days that i want it to, but it seems like it doesn't look at the year. It returns all of the years in the database within the date range. For example, if the user puts in 09/01/2012 for the start date and 09/18/2012 as the end date, it would return those date ranges for the years 2010, 2011 and 2012. Here is my code.

SELECT tblDiscussionLog.CoachDate, tblDiscussionLog.Coach, tblDiscussionLog.DeptofCoach
FROM tblDiscussionLog
WHERE (((Format([CoachDate],"mm/dd/yyyy")) Between [Start Date mm/dd/yyyy] And [End Date mm/dd/yyyy]));

[pbaldy]

Try without the Format() function. It's turning the date into a string.

[hammer187]

How do i do that exactly without creating syntax errors?

[hammer187]

If I just have this Between [Start Date mm/dd/yyyy] And [End Date mm/dd/yyyy]; it doesnt work, it gives me syntax errors.

[pbaldy]

I just said the Format function:


WHERE [CoachDate] Between [Start Date mm/dd/yyyy] And [End Date mm/dd/yyyy]

[hammer187]

I figured it out. I did it in the design view. Just took out the format function... Thanks. It works now.