Displaying SQL Results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 23 Jun 2005 06:35 GMT

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)

Reply to this Message

Brendan Reynolds - 23 Jun 2005 09:37 GMT

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

Signature

Brendan Reynolds (MVP)

> Hello all,
>
[quoted text clipped - 4 lines]
>
> DoCmd.RunSQL (vSql)

Reply to this Message

John Spencer (MVP) - 23 Jun 2005 12:52 GMT

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.

> Public Sub DisplayCount()
>
[quoted text clipped - 24 lines]
> >
> > DoCmd.RunSQL (vSql)

Reply to this Message

Brendan Reynolds - 23 Jun 2005 18:26 GMT

Actually, John, the solution I posted does not require an ADP. It just
requires an ADO reference - which Access 2000 and later add by default. You
and I might remove that reference if we're not using it, but less
experienced developers are unlikely to do so.

Signature

Brendan Reynolds (MVP)

> Brendan gave you a solution for an Access Data Project. If you are using
> an
[quoted text clipped - 49 lines]
>> >
>> > DoCmd.RunSQL (vSql)

Reply to this Message