The field I need to add is Test.DateClosed I've tried messing around and trying to mimick the other SELECT statements to make them include this field, but have not been able to get it to work correctly. I assume it needs to be in both mySQL = statements but not sure how to get it to work correctly...
Here is what I have:
Here is what I have:
Code:
Dim mySQL As String
mySQL = "SELECT Test.FirstName, Test.LastName, Test.TSOID, Order_Project, myRand INTO LocalTable " & _
"FROM (SELECT Test.FirstName, Test.LastName, Test.TSOID, Order_Project, rnd(Order_project) AS myRand " & _
"FROM (SELECT DISTINCT Test.FirstName, Test.LastName, Test.TSOID, IIf(IsNull([ProjectNbr]),[OrderNbr],[ProjectNbr]) AS Order_Project FROM Test) AS [%$##@_Alias]) AS [%$##@_Alias] " & _
"ORDER BY myRand;"
DoCmd.SetWarnings (WarningsOff)
DoCmd.RunSQL mySQL
DoCmd.SetWarnings (WarningsOn)
mySQL = "" & _
"SELECT [Order_Project] as [Ticket Number], TSOID, LastName, FirstName " & _
"FROM LocalTable " & _
"WHERE [Order_Project] In " & _
"(SELECT TOP " & tmpIB & " [Order_Project] " & _
" FROM LocalTable AS DTest_aux " & _
" WHERE DTest_aux.TSOID = LocalTable.TSOID ORDER BY myRand) " & _
"ORDER BY TSOID, Order_Project, LastName, FirstName;"
CurrentDb().QueryDefs("Top3").SQL = mySQL
End Sub