SQL Error

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

Hi -

I have a problem with an SQL statement I am trying to
execute in VBA.
This SQL statement was generated by the 'Design Query'
tool in Access.

The error reads: "A RunSQL action requires an argument
consisting of an SQL statement"
Thus I think my SQL statement might have an operator (or
something) missing.

SELECT
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GROUP
, Sum(IIf([% Grade]>=70,1,0)) AS Pass, Sum(IIf([% Grade]
<70,1,0)) AS Fail,
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE,
qry_Did_Not_Take.SumOfANSWERED, STUDENT_FIELDS.FIELD3 AS
Region, STUDENT_FIELDS.FIELD4 AS Country,
STUDENT_FIELDS.FIELD5 AS Division, STUDENT_FIELDS.FIELD2
AS Director, STUDENT_FIELDS.FIELD8 AS Function,
STUDENT_FIELDS.FIELD1 AS Name
FROM (R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP INNER
JOIN STUDENT_FIELDS ON
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE =
STUDENT_FIELDS.STUDENT_ID) LEFT JOIN qry_Did_Not_Take ON
STUDENT_FIELDS.STUDENT_ID = qry_Did_Not_Take.EMPLOYEE
GROUP BY
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GROUP
, R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE,
qry_Did_Not_Take.SumOfANSWERED, STUDENT_FIELDS.FIELD3,
STUDENT_FIELDS.FIELD4, STUDENT_FIELDS.FIELD5,
STUDENT_FIELDS.FIELD2, STUDENT_FIELDS.FIELD8,
STUDENT_FIELDS.FIELD1
HAVING
(((R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GR
OUP)<>"COMMODITY KNOWLEDGE/STRATEGY") AND
((qry_Did_Not_Take.SumOfANSWERED) Is Null) AND
((STUDENT_FIELDS.FIELD3)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Region]) AND ((STUDENT_FIELDS.FIELD4)=
[forms]![frm_Reports]![cmb_Pass_Fail_Country]) AND
((STUDENT_FIELDS.FIELD5)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Division]) AND ((STUDENT_FIELDS.FIELD2)=
[forms]![frm_Reports]![cmb_Pass_Fail_Director]) AND
((STUDENT_FIELDS.FIELD8)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Function]) AND ((STUDENT_FIELDS.FIELD1)=
[forms]![frm_Reports]![cmb_Pass_Fail_Student]))
ORDER BY STUDENT_FIELDS.FIELD3, STUDENT_FIELDS.FIELD4,
STUDENT_FIELDS.FIELD5, STUDENT_FIELDS.FIELD2,
STUDENT_FIELDS.FIELD1;


Please Advise! Thanks!!
 
Hi Leo,

Sounds like you are using Docmd.RunSQL, but that is only
used for executing action queries (such as delete,
append, make table). Your sql appears to be just a plain
select statement. You didn't say what you want to do
with the query. If you want to open it, you might try
docmd.openquery.

HTH, Ted Allen
 
Hi,

Thanks. What If I want to run the query (because I have a
report that 'reads' from this query)?
Should it be Docmd.Run SQL? But the error "A RunSQL action
requires an argument consisting of an SQL statement"
appears.

I think an argument is missing?

Please help!


-----Original Message-----
Hi Leo,

Sounds like you are using Docmd.RunSQL, but that is only
used for executing action queries (such as delete,
append, make table). Your sql appears to be just a plain
select statement. You didn't say what you want to do
with the query. If you want to open it, you might try
docmd.openquery.

HTH, Ted Allen
-----Original Message-----
Hi -

I have a problem with an SQL statement I am trying to
execute in VBA.
This SQL statement was generated by the 'Design Query'
tool in Access.

The error reads: "A RunSQL action requires an argument
consisting of an SQL statement"
Thus I think my SQL statement might have an operator (or
something) missing.

SELECT
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GROU P
, Sum(IIf([% Grade]>=70,1,0)) AS Pass, Sum(IIf([% Grade]
<70,1,0)) AS Fail,
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE,
qry_Did_Not_Take.SumOfANSWERED, STUDENT_FIELDS.FIELD3 AS
Region, STUDENT_FIELDS.FIELD4 AS Country,
STUDENT_FIELDS.FIELD5 AS Division, STUDENT_FIELDS.FIELD2
AS Director, STUDENT_FIELDS.FIELD8 AS Function,
STUDENT_FIELDS.FIELD1 AS Name
FROM (R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP INNER
JOIN STUDENT_FIELDS ON
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE =
STUDENT_FIELDS.STUDENT_ID) LEFT JOIN qry_Did_Not_Take ON
STUDENT_FIELDS.STUDENT_ID = qry_Did_Not_Take.EMPLOYEE
GROUP BY
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GROU P
, R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE,
qry_Did_Not_Take.SumOfANSWERED, STUDENT_FIELDS.FIELD3,
STUDENT_FIELDS.FIELD4, STUDENT_FIELDS.FIELD5,
STUDENT_FIELDS.FIELD2, STUDENT_FIELDS.FIELD8,
STUDENT_FIELDS.FIELD1
HAVING
(((R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GR
OUP)<>"COMMODITY KNOWLEDGE/STRATEGY") AND
((qry_Did_Not_Take.SumOfANSWERED) Is Null) AND
((STUDENT_FIELDS.FIELD3)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Region]) AND ((STUDENT_FIELDS.FIELD4)=
[forms]![frm_Reports]![cmb_Pass_Fail_Country]) AND
((STUDENT_FIELDS.FIELD5)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Division]) AND ((STUDENT_FIELDS.FIELD2)=
[forms]![frm_Reports]![cmb_Pass_Fail_Director]) AND
((STUDENT_FIELDS.FIELD8)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Function]) AND ((STUDENT_FIELDS.FIELD1)=
[forms]![frm_Reports]![cmb_Pass_Fail_Student]))
ORDER BY STUDENT_FIELDS.FIELD3, STUDENT_FIELDS.FIELD4,
STUDENT_FIELDS.FIELD5, STUDENT_FIELDS.FIELD2,
STUDENT_FIELDS.FIELD1;


Please Advise! Thanks!!
.
Click to expand...
.
Click to expand...
 
Hi Leo,

Opening a report that is based on a query will
automatically cause the source query to execute. Try
just opening or printing the report directly.

HTH, Ted Allen
-----Original Message-----
Hi,

Thanks. What If I want to run the query (because I have a
report that 'reads' from this query)?
Should it be Docmd.Run SQL? But the error "A RunSQL action
requires an argument consisting of an SQL statement"
appears.

I think an argument is missing?

Please help!


-----Original Message-----
Hi Leo,

Sounds like you are using Docmd.RunSQL, but that is only
used for executing action queries (such as delete,
append, make table). Your sql appears to be just a plain
select statement. You didn't say what you want to do
with the query. If you want to open it, you might try
docmd.openquery.

HTH, Ted Allen
-----Original Message-----
Hi -

I have a problem with an SQL statement I am trying to
execute in VBA.
This SQL statement was generated by the 'Design Query'
tool in Access.

The error reads: "A RunSQL action requires an argument
consisting of an SQL statement"
Thus I think my SQL statement might have an operator (or
something) missing.

SELECT
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GR
Click to expand...
OU
P
, Sum(IIf([% Grade]>=70,1,0)) AS Pass, Sum(IIf([% Grade]
<70,1,0)) AS Fail,
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE,
qry_Did_Not_Take.SumOfANSWERED, STUDENT_FIELDS.FIELD3 AS
Region, STUDENT_FIELDS.FIELD4 AS Country,
STUDENT_FIELDS.FIELD5 AS Division, STUDENT_FIELDS.FIELD2
AS Director, STUDENT_FIELDS.FIELD8 AS Function,
STUDENT_FIELDS.FIELD1 AS Name
FROM (R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP INNER
JOIN STUDENT_FIELDS ON
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE =
STUDENT_FIELDS.STUDENT_ID) LEFT JOIN qry_Did_Not_Take ON
STUDENT_FIELDS.STUDENT_ID = qry_Did_Not_Take.EMPLOYEE
GROUP BY
R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GR
Click to expand...
OU
P
, R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.EMPLOYEE,
qry_Did_Not_Take.SumOfANSWERED, STUDENT_FIELDS.FIELD3,
STUDENT_FIELDS.FIELD4, STUDENT_FIELDS.FIELD5,
STUDENT_FIELDS.FIELD2, STUDENT_FIELDS.FIELD8,
STUDENT_FIELDS.FIELD1
HAVING
Click to expand...
(((R1B_PARTICIPANT_SCORE_BY_COMPETENCY_GROUP.COMPETENCY_GR
OUP)<>"COMMODITY KNOWLEDGE/STRATEGY") AND
((qry_Did_Not_Take.SumOfANSWERED) Is Null) AND
((STUDENT_FIELDS.FIELD3)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Region]) AND ((STUDENT_FIELDS.FIELD4)=
[forms]![frm_Reports]![cmb_Pass_Fail_Country]) AND
((STUDENT_FIELDS.FIELD5)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Division]) AND ((STUDENT_FIELDS.FIELD2)=
[forms]![frm_Reports]![cmb_Pass_Fail_Director]) AND
((STUDENT_FIELDS.FIELD8)=[forms]![frm_Reports]!
[cmb_Pass_Fail_Function]) AND ((STUDENT_FIELDS.FIELD1)=
[forms]![frm_Reports]![cmb_Pass_Fail_Student]))
ORDER BY STUDENT_FIELDS.FIELD3, STUDENT_FIELDS.FIELD4,
STUDENT_FIELDS.FIELD5, STUDENT_FIELDS.FIELD2,
STUDENT_FIELDS.FIELD1;


Please Advise! Thanks!!
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top