Access Data Type Mismatch in Criteria Expression

Question

I'm seeing some strange behavior in a C# TableAdapter that is pulling from Access. I have a query that returns results in Access, returns results in the TableAdapter Query Builder Execute Query window, but fails in code, and fails when using Preview Data dialog.

The issue seems to be in the DateTime parameter. What is strange is that it only fails when put inside the nested select statment. It works fine when applied to the whole query. Unfortunately I need it in the nested portion, but I always get the "Data Type Mismatch in Criteria Expression". The query also works if I remove the other parameter, and leave the data parameter in the nested query.

Works:

SELECT        Employee.FirstName, TimeCard.ID
FROM            (((Employee INNER JOIN
                     MasterFilmUnit ON Employee.ID = MasterFilmUnit.ID) INNER JOIN
                     MasterDepartment ON Employee.ID = MasterDepartment.ID) LEFT OUTER JOIN
                         (SELECT        ID, WorkDate, EmployeeID
                           FROM            DailyTimeCard
                           WHERE (WorkDate = ?)) TimeCard ON TimeCard.EmployeeID = Employee.ID)

Works:

SELECT        Employee.FirstName, TimeCard.ID
FROM            (((Employee INNER JOIN
                     MasterFilmUnit ON Employee.ID = MasterFilmUnit.ID) INNER JOIN
                     MasterDepartment ON Employee.ID = MasterDepartment.ID) LEFT OUTER JOIN
                         (SELECT        ID, WorkDate, EmployeeID
                           FROM            DailyTimeCard
                           ) TimeCard ON TimeCard.EmployeeID = Employee.ID)
WHERE Employee.ProjectID=? AND WorkDate = ?

Doesn't Work:

SELECT        Employee.FirstName, TimeCard.ID
FROM            (((Employee INNER JOIN
                     MasterFilmUnit ON Employee.ID = MasterFilmUnit.ID) INNER JOIN
                     MasterDepartment ON Employee.ID = MasterDepartment.ID) LEFT OUTER JOIN
                         (SELECT        ID, WorkDate, EmployeeID
                           FROM            DailyTimeCard
                          WHERE WorkDate = ? ) TimeCard ON TimeCard.EmployeeID = Employee.ID)
WHERE Employee.ProjectID=?

EDIT

I've been able to get the desired output by nesting the employee portion of the query, and therefore not having a where statement apply to the whole query. I'm up and running now, but all the same, I can't figure out why the original isn't working:

Works:

SELECT        Employee.FirstName, TimeCard.ID
FROM            ((((SELECT Employee.*
                          FROM            Employee Employee_1
                          WHERE        (ProjectID = ?)) Employee Left OUTER JOIN
                     MasterFilmUnit ON Employee.ID = MasterFilmUnit.ID) LEFT OUTER JOIN
                     MasterDepartment ON Employee.ID = MasterDepartment.ID) LEFT OUTER JOIN
                         (SELECT        DailyTimeCard.*
                           FROM            DailyTimeCard
                           WHERE        (WorkDate = ?)) TimeCard ON TimeCard.EmployeeID = Employee.ID)

Any help would be appreciated. I'm stumped.

Answer 1

The ? parameter placeholders are processed in the order in which they appear, so the this query would be expecting parameter 1 to be WorkDate and parameter 2 to be ProjectID

If this is still not working, try wrapping the ? parameter markers in CDate() and/or (presumably) CInt() to force the parameters to the correct datatype(s).