counting records in a table

how exactly do i count the records in a table by a date.

the date is entered as a number long

yyyymmdd


i want to use a variable for the date

any help is greatly appreciated

[Wen Lie]

I can't exactly know what you mean...
But maybe this will help...

Try to save the date in a variable...
then..
Open the table which date is same with the contents in that variable.
If, open succesful (Records found), then, you loop it....

e.g :

dim vardate as date

vardate=datefield

MyConn.Open "DSN=dsnName"
MyRec.Open "select * from tablename where datefield = '" & vardate & "'", MyConn, adOpenDynamics, adLockOptimistic, adCmdText

If (MyRec.BOF<>True) and (MyRec.EOF<>True) then
MyRec.MoveFirst
RecCounter = 0
Do While Not MyRec.EOF
RecCounter = RecCounter + 1
MyRec.MoveNext
Loop
Else
msgbox "Record not found"
Endif
MyRec.Close

I hope it would Help you......
Ciaooooo
Best Regards,

Wen Lie

[MartinE]

There is an much easier way - "SELECT count (*) FROM " & tablename & " WHERE date=" & date.

With this statement you will get a recordset with just one entry - the recordcount

[Jamppa]

Answer wich MartinE gave you isn´t just much easier but also more effective and faster way of counting recordsets.

Jan