SQL Where clause based on combo box on form (1 Viewer)

[TB11]

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]))

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.

 

[theDBguy]

Which query did you want to restrict with a WHERE clause? The subquery or the outer query?

PS. Also, I don't think you need the second GROUP BY clause.

 

[TB11]

@theDBguy I would like the WHERE to be on the outer query.

 

[theDBguy]

@theDBguy I would like the WHERE to be on the outer query.

Just a guess...

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
  WHERE test.fkCompany=[Forms]![fTest]![cboCompany]