Automatically Manipulate a Query in a Module?

[CopperWire]

I'm currently using a form to set the criteria in a query and I'm wondering if there is a way to do that with a module. Specifically, I want to change the date in the WHERE statement in the code below:

Private Sub Calculate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Scheduling_data.* FROM Scheduling_data WHERE Scheduling_data.batch_dt >= #9/16/2003# ORDER BY Scheduling_data.cd_wr, Scheduling_data.batch_dt;"

Dim valueA, valueB As Long 'WR #
Dim valueC, valueD As Date 'Date Scheduled
Dim valueE, valueF As Date 'Batch Date
Dim valueG, valueH As Long ' Days Diff
Dim valueI As Long 'Date Diff Batch Date
Dim valueJ As Long 'Date Diff Days Diff

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveLast
.MoveFirst
While Not rst.EOF
valueA = rst![cd_wr]
valueE = rst![batch_dt]
valueG = rst![days_diff]

.MoveNext
If Not rst.EOF Then
valueB = rst![cd_wr]
valueF = rst![batch_dt]
valueH = rst![days_diff]

If valueA = valueB Then
valueI = DateDiff("d", valueE, valueF)
valueJ = DateDiff("d", valueH, valueG)
End If

If valueA = valueB Then
rst.Edit
rst![date_diff] = Abs(valueJ - valueI)
rst.Update
End If

If valueA = valueB And valueG < valueH Then
rst.Edit
rst![least] = valueG
rst.Update

Else

rst.Edit
rst![least] = valueH
rst.Update

End If
End If

Wend
End With


Call MoreCalculate(rst)

Set rst = Nothing
Set dbs = Nothing


End Sub

 

[RoyVidar]

Hi!

Something like this:
...WHERE Scheduling_data.batch_dt >= #&quot; & Forms!YourForm!YourDateControl_1 & &quot;# ORDER BY Scheduling_data.cd_wr, Scheduling_data.batch_dt;&quot;

or

...WHERE Scheduling_data.batch_dt >= #&quot; & dtYourDateVariable & &quot;# ORDER BY Scheduling_data.cd_wr, Scheduling_data.batch_dt;&quot;

Note - the date must use US date formatting

HTH Roy-Vidar

 

[CopperWire]

Thank you I'll give that a try!