Can't find what's wrong with this SQL

  • Thread starter Thread starter Alp Bekisoglu
  • Start date Start date
A

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
 
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.
 
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

Allen Browne said:
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.

Alp Bekisoglu said:
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
Click to expand...
Click to expand...
 
Back
Top