If TblTrialsMainDa ta is the source table for subformB then the following
sql string should work:
strSql = "Select * from TblRunSpecs Where _
& "(TrialDate In (Select TrialDate " _
& "From TblTrialsMainDa ta Group By TrialDate)) And " _
& "(CompanyKe y In (Select CompanyKey From " _
& "TblTrialsMainD ata Group By CompanyKey))"
This code assumes that TblRunSpecs contains a bulk of data and you want
to retrieve a subset of records from TblRunSpecs where the records
contain TrialDates and CompanyKey the same as the TrialDates and
CompanyKey from TblTrialsMainDa ta
Again, the two subqueries in the sql string above will retrieve all the
unique TrialDates from TblTrialsMainDa ta and all the unique CompanyKeys
from TblTrialsMainDa ta.
Before implementing this sql into your project you should test it as
follows:
Sub testSql()
Dim strSql As String, RS AS DAO.Recordset
strSql = "Select * from TblRunSpecs Where _
& "(TrialDate In (Select TrialDate " _
& "From TblTrialsMainDa ta Group By TrialDate)) And " _
& "(CompanyKe y In (Select CompanyKey From " _
& "TblTrialsMainD ata Group By CompanyKey))"
Set RS = CurrentDB.OpenR ecordset(strSql )
RS.MoveLast
Msgbox RS.RecordCount
End Sub
In your hardcoded query, the number of records you get from that should
match RS.RecordCount.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!