DCOUNT programming

[Mike]

Trying to use dcount to see how many records are in a
table matching the customer id and todays date. If I just
use the id for criteria, I get a count. But when I use a
date, error. Any ideas appreciated.

 

[tina]

when you set the criteria for date, make sure you enclose the date or date
reference with # before and after.

 

[TC]

Show the code! We ain't got ESP :-(

TC

 

[Ike]

You didnt ask but....

I wouldnt use dcount. i would query the database and get a record
count. this way with the connection open you could read the data if
you want. try the code below. Also, view the online help if you dont
understand some of it.

Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM

"
stSql = stSql & " WHERE [FIELD_A] =" & Me.FIELD_A ' if this is
a string you have to add single quotes
stSql = stSql & " ORDER BY [ItemNumber];"
Set RS = CreateObject("ADODB.Recordset")
RS.Open stSql, con, 1 ' 1 = adOpenKeyset

intRecordcount = rs.recordcount

 

[Dirk Goldgar]

You didnt ask but....

I wouldnt use dcount. i would query the database and get a record
count. this way with the connection open you could read the data if
you want. try the code below. Also, view the online help if you dont
understand some of it.

Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM

"
stSql = stSql & " WHERE [FIELD_A] =" & Me.FIELD_A ' if this is
a string you have to add single quotes
stSql = stSql & " ORDER BY [ItemNumber];"
Set RS = CreateObject("ADODB.Recordset")
RS.Open stSql, con, 1 ' 1 = adOpenKeyset

intRecordcount = rs.recordcount

Seems to me that would be much less efficient than just querying the
count (whether by SELECT COUNT(*) or by DCount), unless you *do* intend
to process the individual records in the recordset.