Displaying SQL Results

[Guest]

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)

 

[Brendan Reynolds]

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

 

[John Spencer (MVP)]

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()

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)

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)

 

[Brendan Reynolds]

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.

--
Brendan Reynolds (MVP)

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()

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)

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)