Brendan gave you a solution for an Access Data Project. If you are using
an
.mdb (DAO) you need a slightly different solution.
One thing you might look at is the DCount Function
SomeVariable = DCount("WORK_REQ_ID","VIAWARE_WCS_TO_VIA_T","DTIMEMOD
Between
[cal1] And [cal2]")
If Cal1 and Cal2 are parameters you are inputing then you will need to
construct
the "Where" string with the proper delimiters and the values of Cal1 and
Cal2.
Something like the following. Also Access in this situation expects dates
to be
in US mm/DD/yyyy format or in yyyy/mm/dd format.
"DTIMEMOD Between #" & [cal1] & "# And #" & [cal2] & #"
If you are trying to get a count of the unique values for Work_Req_ID then
post
back as this shouldn't do that.
Brendan said:
Public Sub DisplayCount()
Dim rst As ADODB.Recordset
Dim lngCount As Long
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Count(*) AS TheCount FROM tblTest"
.Open
lngCount = .Fields("TheCount")
.Close
End With
MsgBox "The count was: " & CStr(lngCount)
End Sub
--
Brendan Reynolds (MVP)
Mark said:
Hello all,
Is there a way to display the results of a query when doing a count?
vSql = "SELECT distinctrow Count(WORK_REQ_ID) AS CountOfWORK_REQ_ID
FROM
VIAWARE_WCS_TO_VIA_T WHERE (((DTIMEMOD) Between [cal1] And [cal2]))"
DoCmd.RunSQL (vSql)