WHERE clause kills the whole query

[Margarita]

Hello,
I have a query that works just fine but when I put it a where condition, a blank results screen appears. I have tried putting in the condition in the subquery in the from clause and also in the main query, but nothing works. As soon as I take out the where condition, everything works, but I need it in there- so I need to figure out why this clause is killing the whole query.
The query is as follows:

SELECT LastName, FirstName, Role, InvoiceNum, ServiceSubcat, FTE, FTEAllocatAmountProduct, FMS_PayReq.[Hdr-Doc ID], FTEMonthStart
FROM [Select TEMPS_3_true_and_allocation_salary_perInvEmployee.LAstName, TEMPS_3_true_and_allocation_salary_perInvEmployee.FirstName, TEMPS_3_true_and_allocation_salary_perInvEmployee.Role, TEMPS_3_true_and_allocation_salary_perInvEmployee.InvoiceNum, EmployeeTask_FTE.ServiceSubcat,
FTEMonthStart,
EmployeeTask_FTE.FTE, FTE*AllocatMonthSalaryperEmployee AS FTEAllocatAmountProduct
FROM
TEMPS_3_true_and_allocation_salary_perInvEmployee INNER JOIN EmployeeTask_FTE ON (TEMPS_3_true_and_allocation_salary_perInvEmployee.LAstName = EmployeeTask_FTE.LastName) AND (TEMPS_3_true_and_allocation_salary_perInvEmployee.FirstName = EmployeeTask_FTE.FirstName) AND (TEMPS_3_true_and_allocation_salary_perInvEmployee.WorkTimeType = EmployeeTask_FTE.WorkTimeType)
]. AS MainSub INNER JOIN FMS_PayReq ON MainSub.InvoiceNum= FMS_PayReq.[Comm-Invoice No]
WHERE FTEMonthStart between [start date] and [start date];

The Field FTEMonthStart is a date field in the table EmployeeTask_FTE. I checked- the format of the field in the table is correct.
Any thoughts on where this is going wrong?

Thank you so much!

 

[bob fitz]

Have you tried:
WHERE FTEMonthStart between Format([start date],"mm/dd/yyyy") and Format([start date],"mm/dd/yyyy")

 

[Margarita]

Have you tried:
WHERE FTEMonthStart between Format([start date],"mm/dd/yyyy") and Format([start date],"mm/dd/yyyy")

Hi Bob, just tried it upon your suggestion. Unfortunately- didn't help- getting the same blank results screen.

Thanks for giving this a shot!

 

[bob fitz]

Are you sure there is data to be returned between the stated dates?
I know this seems like a stupid question, but sometimes we just miss the obvious.

 

[Margarita]

Hehe- yes, I did check for my own stupidity (this time- it took a while to learn to do that). But no- missing data is not the issue here. There is most definitely data in all months between June 2011 and July 2012. I tried a few different date values the problem is the same for all of them. When I take out the date criteria, I get what I expect. But I do need it filtered by only one month at a time and since this is to be used progrmatically in vba, I need the parameters to work properly.
When I select data from the EmployeeTask_FTE table itself using the same date parameter, I get results as I expect. But it doesn't work in this query. Both the subqueries in the from clause of this query work as expected.
Thanks!

 

[Pat Hartman]

WHERE FTEMonthStart between [start date] and [start date];

You are using the same start and end date so the only date that will satisfy the criteria is the actual start date.

Also, if your dates contain time, you need to extract only the date portion when using the date as criteria.

WHERE DateValue(FTEMonthStart) between [start date] and [start date];

 

[Margarita]

Hi Pat,
The between clause is not the only thing I tried- I also tried 'where FTEmonthstart = [start date]' since that field only contains the frist dates of the month- setting is as equal should work. So even the between clause as I have it written should, theoretically work.
I made a little bit of progress on this. I edited one of the subqueries- TEMPS_3_true_and_allocation_salary_perInvEmployee- which involves the EmployeeTask_FTE table directly (and the main query that I posted above does not reference that table directly)- and included the where clause in the subquery. It now works. However, I am now having a similar problem with another where clause, also from the FTE table. I will post back if I solve my problem.
Thanks!

 

[Pat Hartman]

Does the date field include time?

Does the query return rows without the where clause?