AC2007:Main query, dependant query, and Dlookup in text control not working

[EdNerd]

I am trying to get the first, second, third, and fourth values into text box controls. Here's the steps I have so far:
-- I have a Top 4 query to return the top four records.
-- I run a Min query against those results to return the fourth record.
-- I use Dlookup in a text box control (Control Source property) to pull an individual field from the fourth record.

If I run the two queries in sequence with a specific criteria, the Dlookup returns the correct response.

I need to pass the criteria from the form into the Top 4 query, then again into the Min query, and finally into the Dlookup. And it's not working.

Is it possible to merge the two queries into one? "Get the Min of this Top 4"? I use Dlookup against a query successfully in another control; it's a pretty simple and straightforward query, though. This seems pretty complex.

Edit:
I tried putting the SQLs into a Function, but couldn't make that work, either. It looks like this:

 Public Function TopMinFLD4(meNum As Long) As Variant
Dim strSQL1 As String
Dim strSQL2 As String
strSQL1 = "SELECT TOP 4 tblEmplData.EmplNum, tblEmplData.LName, " & _
          "tblEmplData.EmplType, tblReviewPerf.ReviewName, " & _
          "tblReviewPerf.dtRevPerf, tblReviewPerf.numPointsPoss, " & _
          "tblReviewPerf.numPointsTotal, [numpointsposs]/[numpointstotal] AS [Avg], " & _
          "tblReviewPerf.TIRontime, tblReviewPerf.Reviewer " & _
          "FROM tblEmplData INNER JOIN (tblReviews INNER JOIN " & _
          "tblReviewPerf ON tblReviews.ReviewName = tblReviewPerf.ReviewName) ON " & _
          "tblEmplData.EmplNum = tblReviewPerf.EmplNum " & _
          "WHERE (((tblEmplData.EmplType)=" & Chr(34) & "DC" & Chr(34) & _
          " Or (tblEmplData.EmplType)=" & Chr(34) & "FL" & Chr(34) & ") AND " & _
          "((tblEmplData.EmplNum)=meNum) AND ((tblReviewPerf.ReviewName)=" & Chr(34) & _
          "FLD" & Chr(34) & ") AND ((tblReviewPerf.dtRevPerf) Is Not Null) AND " & _
          "((tblReviewPerf.numPointsPoss) Is Not Null) AND " & _
          "((tblEmplData.chkIsActive)=Yes)) " & _
          "ORDER BY tblReviewPerf.dtRevPerf DESC;"
DoCmd.RunSQL strSQL1
strSQL2 = "SELECT qryEmplRev_TOP_FLD_4.EmplNum, qryEmplRev_TOP_FLD_4.LName, " & _
          "qryEmplRev_TOP_FLD_4.EmplType, qryEmplRev_TOP_FLD_4.ReviewName, " & _
          "Min(qryEmplRev_TOP_FLD_4.dtRevPerf) AS MinOfdtRevPerf, " & _
          "Last(qryEmplRev_TOP_FLD_4.numPointsPoss) AS LastOfnumPointsPoss, " & _
          "Last(qryEmplRev_TOP_FLD_4.numPointsTotal) AS LastOfnumPointsTotal, " & _
          "Last(qryEmplRev_TOP_FLD_4.Avg) AS LastOfAvg, " & _
          "Last(qryEmplRev_TOP_FLD_4.TIRontime) AS LastOfTIRontime, " & _
          "Last(qryEmplRev_TOP_FLD_4.Reviewer) AS LastOfReviewer " & _
          "FROM qryEmplRev_TOP_FLD_4 " & _
          "GROUP BY qryEmplRev_TOP_FLD_4.EmplNum, qryEmplRev_TOP_FLD_4.LName, " & _
          "qryEmplRev_TOP_FLD_4.EmplType, qryEmplRev_TOP_FLD_4.ReviewName " & _
          "HAVING (((qryEmplRev_TOP_FLD_4.EmplNum)=meNum));"
DoCmd.RunSQL strSQL2
MsgBox "done!"
End Function

Any suggestions?
Ed

 

[michaeljryan78]

"((tblEmplData.EmplNum)=meNum) AND ((tblReviewPerf.ReviewName)=" & Chr(34) & _
should be
"((tblEmplData.EmplNum)= " & meNum & ") AND ((tblReviewPerf.ReviewName)=" & Chr(34) & _