unvalid sql

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

 

[Dirk Goldgar]

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)

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

 

[Jacco]

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)

Thank you, it's the second option.