Syntex Error, Missing Operator !!!
-
Dear All, Am getting sytex error , Missing operator on the following: sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In select c_number FROM tb_c_details WHERE e_id = " & eid set rst = db.openrecordset (sqls) Cant find any missing operator :~ , also i used a msgbox to show sqls , the statement is perfect. Helpppp !!! :(
0 will always beats the 1.
-
Dear All, Am getting sytex error , Missing operator on the following: sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In select c_number FROM tb_c_details WHERE e_id = " & eid set rst = db.openrecordset (sqls) Cant find any missing operator :~ , also i used a msgbox to show sqls , the statement is perfect. Helpppp !!! :(
0 will always beats the 1.
scorp_scorp wrote:
Helpppp !!!
All examples I've seen have parentheses following the IN keyword... :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
scorp_scorp wrote:
Helpppp !!!
All examples I've seen have parentheses following the IN keyword... :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
Modified to: sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In ( select c_number FROM tb_c_details WHERE e_id = " & eid & " )" Didnt work ... same problem :(( :(( Any different statment that would give me the same results ??? since i think the error is from the IN phrase .
0 will always beats the 1.
modified on Sunday, February 13, 2011 2:51 AM
-
Dear All, Am getting sytex error , Missing operator on the following: sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In select c_number FROM tb_c_details WHERE e_id = " & eid set rst = db.openrecordset (sqls) Cant find any missing operator :~ , also i used a msgbox to show sqls , the statement is perfect. Helpppp !!! :(
0 will always beats the 1.
If you alreasy corrected the in clause, are the scope and eid variables containing character values? If theay are they should be between ''. You could also post the contents of the sqls variable in order to see what's the actual statement that's executed against the database.
The need to optimize rises from a bad design.My articles[^]
-
If you alreasy corrected the in clause, are the scope and eid variables containing character values? If theay are they should be between ''. You could also post the contents of the sqls variable in order to see what's the actual statement that's executed against the database.
The need to optimize rises from a bad design.My articles[^]
scope is a string and eid is a int. dim scope as string dim eid as integer scope = CStr(Forms![report_testin]![Combo42].Value) eid =Int(Forms![report_testin]![Combo29].Value) ami defining the string sqls wrong??? I also tried to modify sqls to be: sqls = "SELECT distinct c_number, c_description FROM tb_c INNER JOIN tb_c_details ON tb_c.c_number = tb_c_details.c_number WHERE e_id = " & eid It generates another Error: "The specifies Field 'c_number' could refer to more than one table listed in the FROM clause of your sql statement" but it doesnt make sense for me :wtf:
0 will always beats the 1.
modified on Sunday, February 13, 2011 4:08 AM
-
scope is a string and eid is a int. dim scope as string dim eid as integer scope = CStr(Forms![report_testin]![Combo42].Value) eid =Int(Forms![report_testin]![Combo29].Value) ami defining the string sqls wrong??? I also tried to modify sqls to be: sqls = "SELECT distinct c_number, c_description FROM tb_c INNER JOIN tb_c_details ON tb_c.c_number = tb_c_details.c_number WHERE e_id = " & eid It generates another Error: "The specifies Field 'c_number' could refer to more than one table listed in the FROM clause of your sql statement" but it doesnt make sense for me :wtf:
0 will always beats the 1.
modified on Sunday, February 13, 2011 4:08 AM
If scope is a string your statement should be something like
sqls = "SELECT c_number, c_description, c_scope, c_category, c_type " & _
FROM tb_c " & _
WHERE c_scope = '" & scope & "' " & _
AND c_number IN ( SELECT c_number " & _
FROM tb_c_details " & _
WHERE e_id = " & eid & " )"Also instead of using literals (concatenated values) in your statement you should use bind variables. This way you're more safe from SQL injections, datatype mismatches, syntax errors etc [addition] Concenring the latter error, you should use aliases in the select portion. Like:
SELECT distinct tb_c.c_number, tb_c.c_description ...
The need to optimize rises from a bad design.My articles[^]
-
If scope is a string your statement should be something like
sqls = "SELECT c_number, c_description, c_scope, c_category, c_type " & _
FROM tb_c " & _
WHERE c_scope = '" & scope & "' " & _
AND c_number IN ( SELECT c_number " & _
FROM tb_c_details " & _
WHERE e_id = " & eid & " )"Also instead of using literals (concatenated values) in your statement you should use bind variables. This way you're more safe from SQL injections, datatype mismatches, syntax errors etc [addition] Concenring the latter error, you should use aliases in the select portion. Like:
SELECT distinct tb_c.c_number, tb_c.c_description ...
The need to optimize rises from a bad design.My articles[^]
Wooooohoooooo :cool::cool::thumbsup: Thanks a lot Mika ,i appriciate , it worked perfect , and thanks for the Addidtional note too ;)
0 will always beats the 1.
-
Wooooohoooooo :cool::cool::thumbsup: Thanks a lot Mika ,i appriciate , it worked perfect , and thanks for the Addidtional note too ;)
0 will always beats the 1.