unvalid sql

J

Jacco

Hi everyone, I'm trying to run this SQL query with DoCmd.RunSQL but it keeps
giving a error

It should count the amount of aircraft with a lbk_id (a column in table
Aircraft)

Dim count_old_aircraft As String

count_old_aircraft = "SELECT Count(Aircraft.lbk_id) " & _
"AS CountOflbk_id FROM Aircraft " & _
"HAVING (((Count(Aircraft.lbk_id))<>0));"

DoCmd.RunSQL count_old_aircraft


Error:
2342 - A RunSQL action requires a argument consisting of an SQL statement.


Any help is welcome....

Jacco
(and if it works: how do I retrieve that number so I can display it in
MsgBox or something)
 
D

Dirk Goldgar

Jacco said:
Hi everyone, I'm trying to run this SQL query with DoCmd.RunSQL but
it keeps giving a error

It should count the amount of aircraft with a lbk_id (a column in
table Aircraft)

Dim count_old_aircraft As String

count_old_aircraft = "SELECT Count(Aircraft.lbk_id) " & _
"AS CountOflbk_id FROM Aircraft " & _
"HAVING
(((Count(Aircraft.lbk_id))<>0));"

DoCmd.RunSQL count_old_aircraft


Error:
2342 - A RunSQL action requires a argument consisting of an SQL
statement.


Any help is welcome....

Jacco
(and if it works: how do I retrieve that number so I can display it in
MsgBox or something)
Click to expand...

RunSQL only works with action queries, not with SELECT queries. In
general, to get the results of a SELECT query in code, you have to open
a recordset on the query; however, in this case I believe you could get
the information you want by using the DCount function:

Dim lngCount As Long

lngCount = DCount("lbk_id", "Aircraft")

or maybe

lngCount = DCount("lbk_id", "Aircraft", "lbk_id <> 0")
 
J

Jacco

Dirk Goldgar said:
RunSQL only works with action queries, not with SELECT queries. In
general, to get the results of a SELECT query in code, you have to open
a recordset on the query; however, in this case I believe you could get
the information you want by using the DCount function:

Dim lngCount As Long

lngCount = DCount("lbk_id", "Aircraft")

or maybe

lngCount = DCount("lbk_id", "Aircraft", "lbk_id <> 0")

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

(please reply to the newsgroup)
Click to expand...
Thank you, it's the second option.
 
Top