How to put query in controlsource of a form

[Song Su]

I have a query code and tested ok.

SELECT Count(dbo_tblVisits.id) AS CountOfid
FROM dbo_tblVisits
WHERE (((dbo_tblVisits.timeIn)>=[forms]![mainmenu].[cbofrom]) AND
((dbo_tblVisits.timeOut)<=[forms]![mainmenu].[cboto]+1));

But when I put that query as a controlsource of a form control, I got #Name?

Private Sub Form_Load()
Dim strRecordCount As String
strRecordCount = "SELECT Count(dbo_tblVisits.id) AS CountOfid" & _
"From dbo_tblVisits WHERE
(((dbo_tblVisits.timeIn)>=[forms]![mainmenu].[cbofrom])" & _
" AND ((dbo_tblVisits.timeOut)<=[forms]![mainmenu].[cboto]+1))"
Me.TotalHour.ControlSource = strRecordCount
End Sub

Where did I do wrong?

 

[John W. Vinson]

I have a query code and tested ok.

SELECT Count(dbo_tblVisits.id) AS CountOfid
FROM dbo_tblVisits
WHERE (((dbo_tblVisits.timeIn)>=[forms]![mainmenu].[cbofrom]) AND
((dbo_tblVisits.timeOut)<=[forms]![mainmenu].[cboto]+1));

But when I put that query as a controlsource of a form control, I got #Name?

Private Sub Form_Load()
Dim strRecordCount As String
strRecordCount = "SELECT Count(dbo_tblVisits.id) AS CountOfid" & _
"From dbo_tblVisits WHERE
(((dbo_tblVisits.timeIn)>=[forms]![mainmenu].[cbofrom])" & _
" AND ((dbo_tblVisits.timeOut)<=[forms]![mainmenu].[cboto]+1))"
Me.TotalHour.ControlSource = strRecordCount
End Sub

Where did I do wrong?

The Control Source of a textbox can be the name of a field in the Form's
Recordsource; or it can be an expression which returns a value; but it cannot
be a SQL string.

Try setting the Control Source to

=DCount("*", "dbo_tblVisits", "[TimeIn] >= #" & [cboFrom] & "# AND [TimeOut]
<= #" & [cboTo] & "#")

to have the DCount() function replace the functionality of your query.

John W. Vinson [MVP]