Run SQL doesn't recognize my SQL statement

[ribhead]

I made a SQL string and when I go to run it I get an error saying "A run SQL action requires an arguement consisting of an SQL statement." I pasted the SQL string in the SQL view and it ran fine. I'm hoping I am just missing something with text delims or something.

Here is the code that doesn't work.
Sub ADD_HRS()
Dim strSQL As String
strSQL = "SELECT sum([Hrs running prod]) " _
& " AS[xvar]" _
& " FROM tblprod" _
& " WHERE [Machine area] = 'TW10Y';"
DoCmd.RunSQL (strSQL)
MsgBox xvar
End Sub

Here is the SQL statement that produces a field with the correct value.
SELECT sum([Hrs running prod])
AS [xvar]
FROM tblprod
WHERE [Machine area ]= 'TW10Y';

Please let me know if I have enough info.

I have Access 2000 and Windows 2000.




Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm afraid knot.

 

[RoyVidar]

Reason for the errormessage, is that the runsql method of the docmd object is only good for action queries (insert, update...), to retrieve/return values you'll need to either open a recordset, or use one of the domain aggragate functions. Here a DAO sample (needs a reference to Microsoft DAO 3,# Object Library (in VBE - Tools | References))

[tt]dim rs as dao.recordset
' your assigning of string
set rs=currentdb.openrecordset(strSQL)
if (not rs.bof and not rs.eof) then
msgbox rs.fields("xvar").value
end if
rs.close
set rs=nothing[/tt]

Don't know if it matters, but there's a missing space between "AS" and "xvar" (well, it would have mattered without the [brackets]).

Roy-Vidar

 

[PHV]

You may try something like this:
xvar = DSum("[Hrs running prod]", "tblprod", "[Machine area]='TW10Y'")
MsgBox xvar

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244