Can't find what's wrong with this SQL

[Alp Bekisoglu]

Hi Experts,

Might be a very obvious reason but just couldn't figure out why this is not
working. It works when I run the query but refuses to run in VBA.

SQL of the query:
SELECT Sum(tb_bookings.Dom) AS SumOfDom, [Events]![dom_bts]-[SumOfDom] AS
Remaining, Events.dom_bts
FROM Events, tb_bookings
GROUP BY Events.dom_bts;

The code in VBA:
Dim strSQL As String
strSQL = "SELECT Sum(tb_bookings.Dom) AS SumOfDom, (Events.dom_bts-SumOfDom)
AS Remaining, Events.dom_bts" & _
" FROM Events, tb_bookings GROUP BY Events.dom_bts;"
DoCmd.RunSQL strSQL

Error is:
Run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement.

Thanks in advance,

Alp

 

[Allen Browne]

RunSQL applies to action queries.

Try OpenRecordset (for results in code, not visible to user), or assign the
SQL string to a QueryDef and OpenQuery if you want to show the results
directly.

With no JOIN between the 2 tables, you will get a Cartesian product.

 

[Alp Bekisoglu]

Thank you Allen.
First; I forgot the "action query" part. :-(
Second, I was just trying to see if I could display the result a bit quicker
via the code.

Alp

RunSQL applies to action queries.

Try OpenRecordset (for results in code, not visible to user), or assign the
SQL string to a QueryDef and OpenQuery if you want to show the results
directly.

With no JOIN between the 2 tables, you will get a Cartesian product.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Experts,

Might be a very obvious reason but just couldn't figure out why this is not
working. It works when I run the query but refuses to run in VBA.

SQL of the query:
SELECT Sum(tb_bookings.Dom) AS SumOfDom, [Events]![dom_bts]-[SumOfDom] AS
Remaining, Events.dom_bts
FROM Events, tb_bookings
GROUP BY Events.dom_bts;

The code in VBA:
Dim strSQL As String
strSQL = "SELECT Sum(tb_bookings.Dom) AS SumOfDom, (Events.dom_bts-SumOfDom)
AS Remaining, Events.dom_bts" & _
" FROM Events, tb_bookings GROUP BY Events.dom_bts;"
DoCmd.RunSQL strSQL

Error is:
Run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement.

Thanks in advance,

Alp