Storing SQL Count to Variable??

  • Thread starter Thread starter MHiemstra
  • Start date Start date
M

MHiemstra

Am confused on how to store a SQL Count to a variable...

What am I doing wrong here??

Dim intHelp_Id As Integer

intHelp_Id = DoCmd.RunSQL(" Count(SHD_CALL_LOG.HELP_ID) " & _
"FROM SHD_CALL_LOG " & _
"INNER JOIN SHD_CALL_STATUS_HISTORIES ON
(SHD_CALL_LOG.CALL_STATUS_ID = SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID) " &
_
"AND (SHD_CALL_LOG.HELP_ID =
SHD_CALL_STATUS_HISTORIES.HELP_ID) " & _
"WHERE
(((SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID)=[Forms]![SHD_HELP_LOG]![CALL_ST
ATUS_ID]));")
 
MHiemstra said:
Am confused on how to store a SQL Count to a variable...

What am I doing wrong here??

Dim intHelp_Id As Integer

intHelp_Id = DoCmd.RunSQL(" Count(SHD_CALL_LOG.HELP_ID) " & _
"FROM SHD_CALL_LOG " & _
"INNER JOIN SHD_CALL_STATUS_HISTORIES ON
(SHD_CALL_LOG.CALL_STATUS_ID =
SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID) " & _
"AND (SHD_CALL_LOG.HELP_ID =
SHD_CALL_STATUS_HISTORIES.HELP_ID) " & _
"WHERE
(((SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID)=[Forms]![SHD_HELP_LOG]![CAL
L_ST
ATUS_ID]));")
Click to expand...

Several things. You can't use RunSQL to execute anything but an action
query, so you can't get any data back from it. Also, your query, which
should be a SELECT query, doesn't actually have the SELECT keyword in
it. For counts from a single table or stored query, you can use the
DCount function, but for your query you'll need to open a recordset on
the (corrected) SQL statement:

'----- start of suggested code -----
Dim intHelp_Id As Integer
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = _
"SELECT Count(SHD_CALL_LOG.HELP_ID) " & _
"FROM SHD_CALL_LOG " & _
"INNER JOIN SHD_CALL_STATUS_HISTORIES " & _
"ON (SHD_CALL_LOG.CALL_STATUS_ID = " & _
"SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID) " & _
"AND (SHD_CALL_LOG.HELP_ID = " & _
"SHD_CALL_STATUS_HISTORIES.HELP_ID) " & _
"WHERE " & _
"SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID=" & _
[Forms]![SHD_HELP_LOG]![CALL_STATUS_ID]

Set rs = CurrentDb.OpenRecordset(strSQL)

intHelp_Id = rs(0)

rs.Close
Set rs = Nothing
'----- end of suggested code -----

Note that I've assumed that the field CALL_STATUS_ID is a numeric field.
If it's text, the statement that builds the WHERE clause of the SQL
statement must be modified to put quotes around the value coming from
the control on the form.
 
It worked thanks for the lesson.

Dirk Goldgar said:
MHiemstra said:
Am confused on how to store a SQL Count to a variable...

What am I doing wrong here??

Dim intHelp_Id As Integer

intHelp_Id = DoCmd.RunSQL(" Count(SHD_CALL_LOG.HELP_ID) " & _
"FROM SHD_CALL_LOG " & _
"INNER JOIN SHD_CALL_STATUS_HISTORIES ON
(SHD_CALL_LOG.CALL_STATUS_ID =
SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID) " & _
"AND (SHD_CALL_LOG.HELP_ID =
SHD_CALL_STATUS_HISTORIES.HELP_ID) " & _
"WHERE
(((SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID)=[Forms]![SHD_HELP_LOG]![CAL
L_ST
ATUS_ID]));")
Click to expand...

Several things. You can't use RunSQL to execute anything but an action
query, so you can't get any data back from it. Also, your query, which
should be a SELECT query, doesn't actually have the SELECT keyword in
it. For counts from a single table or stored query, you can use the
DCount function, but for your query you'll need to open a recordset on
the (corrected) SQL statement:

'----- start of suggested code -----
Dim intHelp_Id As Integer
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = _
"SELECT Count(SHD_CALL_LOG.HELP_ID) " & _
"FROM SHD_CALL_LOG " & _
"INNER JOIN SHD_CALL_STATUS_HISTORIES " & _
"ON (SHD_CALL_LOG.CALL_STATUS_ID = " & _
"SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID) " & _
"AND (SHD_CALL_LOG.HELP_ID = " & _
"SHD_CALL_STATUS_HISTORIES.HELP_ID) " & _
"WHERE " & _
"SHD_CALL_STATUS_HISTORIES.CALL_STATUS_ID=" & _
[Forms]![SHD_HELP_LOG]![CALL_STATUS_ID]

Set rs = CurrentDb.OpenRecordset(strSQL)

intHelp_Id = rs(0)

rs.Close
Set rs = Nothing
'----- end of suggested code -----

Note that I've assumed that the field CALL_STATUS_ID is a numeric field.
If it's text, the statement that builds the WHERE clause of the SQL
statement must be modified to put quotes around the value coming from
the control on the form.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top