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?