Text box on a form to show number of records with todays date

Hi EE

A little stuck here, I have a from in VB6 and I want to show the number of records in a table with todays date

 Set dbs = New ADODB.Connection
 dbs.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\backend.mdb;"
 dbs.Open

numbertoday.Text = dbs.Execute("SELECT tblholdingdata.date, Count(tblholdingdata.message)") _
& "From tblholdingdata" _
& "GROUP BY tblholdingdata.date" _
& "HAVING (((tblholdingdata.date)=Date()));"

I am probably going the wrong way about this, any ideas?

Thanks in advance
LVL 1
PaulstottAsked:
Who is Participating?
 
EDDYKTConnect With a Mentor Commented:
Set dbs = New ADODB.Connection
 dbs.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\backend.mdb;"
 dbs.Open

dim rs as ADOdb.recordset


set rs = dbs.Execute("SELECT Count(tblholdingdata.message) as count ") _
& "From tblholdingdata" _
& "GROUP BY tblholdingdata.date" _
& "HAVING (((tblholdingdata.date)=Date()));"

numbertoday.Text = rs("count ") & "   " & date()
0
 
vinnyd79Commented:

Here's one way:

Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
rs.Open "Select tblholdingdata.date from tblholdingdata Where tblholdingdata.date=#" & Date & "#", dbs, adOpenForwardOnly, adLockReadOnly
MsgBox rs.RecordCount
rs.Close
Set rs = Nothing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.