Hi. I keep getting errors on the Where clause for a SQL query, to get the max date of the InvDate and grouped on Status field.
The form name is fTest, the combo box on the form is named cboCompany. The where from a (different) select query is WHERE (((test.fkCompany)=[Forms]![fTest]![cboCompany]))
Any suggestions?
Thanks.
The form name is fTest, the combo box on the form is named cboCompany. The where from a (different) select query is WHERE (((test.fkCompany)=[Forms]![fTest]![cboCompany]))
Code:
SELECT test.ID, test.[InvNum], test.[InvDate], test.Status, test.fkCompany
FROM test INNER JOIN (SELECT test.Status, Max(test.[InvDate]) AS MaxOfTDate FROM test GROUP BY test.Status) AS q ON (test.[InvDate]=q.MaxOfTDate) AND (test.Status = q.Status)
GROUP BY test.ID, test.[InvNum], test.[InvDate], test.Status, Test.fkCompany;
Any suggestions?
Thanks.