Passing from Form to Query in Access/SQL/VB

Question

Access 2007 / SQL / VB I have a query:

SELECT Count(*) AS CountOfCR1
FROM PData
WHERE (((PData.DestID)='CR1') And (((PData.AnswerTime)>=Starting)<Ending+1));

I am trying to pass the variables Starting and Ending to the above query from the below form:

Starting = StartDate & " " & StartTime
Ending = EndDate & " " & EndTime
On Error GoTo Err_Command5_Click
Dim stDocName As String
stDocName = "CountOfCR1 : Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

How can I pass Starting and Ending from my form to my query?

Answer 1

The usual way to do this is to refer to the form in the query:

SELECT Count(*) AS CountOfCR1
FROM PData
WHERE PData.DestID='CR1'
And PData.AnswerTime Between Forms!MyForm!StartDate + Forms!MyForm!StartTime
                     And Forms!MyForm!EndDate + Forms!MyForm!SEndTime

It is nearly always best to use a form with the recordsource set to the query or an sql string rather than opening a query. With a form, you can use the Where argument of the OpenForm method.