Referencing a Module object within a query

Hi



I have built a database whose primary fuction is to track future staff movement and resourcing.



The database runs a query based upon the variable [DateField], and requires the inputing of a value for [DateField] i need the query to run 80 times, adding 1 day onto the variable [Datefield] each time. script has been written to allow the query to run 80 times, and also defines the value of [DateField] for each of the queries. However this is referenced in the code simply as DateField, and i cannot link it to my Query



Module code:



Private Sub Command0_Click()

Dim cn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim strSQL As String

Dim strQuery As String

Dim newDate As String

Dim mydate As Date

Dim i As Integer



Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source = c:\Documents and Settings\ken01.000\My Documents\Staffing Database.mdb"



cn.Open

Set rst = New ADODB.Recordset

With rst

.ActiveConnection = cn

.CursorType = adOpenStatic

.LockType = adLockOptimistic

End With



newDate = "#9/29/2003#"



strQuery = "qryDateTest"



For i = 1 To 79

strSQL = "SELECT * FROM " & strQuery & "" & _

" WHERE DateField = (" & newDate & ")"



rst.Open (strSQL)



'do something with the recordset



mydate = CDate((Mid(newDate, 2, 9)))

mydate = mydate + 1

newDate = "#" & CStr(mydate) & "#"

rst.Close

Next i



End Sub



Query:



SELECT tblStaffReq.PayrollNumber, tblStaffReq.Surname, tblStaffReq.Forename, tblStaffReq.[Fte Coefficient], IIf([tblStaffReq]![Date Effective]<=[DateField],[tblStaffReq]![New Project],[tblStaffReq]![CoreGroup]) AS [Determined Project]

FROM tblStaffReq;



If anyone has any ideas how to reference the Variable New date through the Query, please let me know, and i will be extremely grateful.



Kind Regards

Kenny

A quick shot would be to create a table with the dates and then link it to the rest of the query. That what you need?