473,320 Members | 1,881 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Missing Semicolon error

I am trying to run the following update query in code.

UPDATE TableTest SET STATUS = "A" WHERE (Code <> "X") AND (Code <>
"J") ORDER BY DOB;

I am getting an error, "Missing Semicolon (;) at end of SQL statement".
Please tell me what is wrong with the syntax. It worked in Access 97.
But in Access 2003, it is so fussy!

I appreciate your help. Thanks, Erika

Nov 13 '05 #1
4 4212
erika wrote:
I am trying to run the following update query in code.

UPDATE TableTest SET STATUS = "A" WHERE (Code <> "X") AND (Code <>
"J") ORDER BY DOB;

I am getting an error, "Missing Semicolon (;) at end of SQL statement".
Please tell me what is wrong with the syntax. It worked in Access 97.
But in Access 2003, it is so fussy!


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need an ORDER BY clause in an UPDATE statement. Otherwise,
nothing else appears wrong w/ it.

What do you mean you're trying to run it in code? Do you mean in VBA?
Have you set it up like this:

Dim strSQL As String
strSQL = "UPDATE TableTest SET Status = 'A'" & _
"WHERE Code Not In ('X', 'J');"

Note the use of single quotes inside the strSQL. Double quotes delimit
the string; therefore, we must use single quotes inside double quotes.

I changed the criteria to use a more intuitive construct. Read the JET
SQL Reference (Access Help) for more info on the IN predicate.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaWX4echKqOuFEgEQI6qACglhtCE6ceGz5xtXf8cg4NJa K7XGMAn3+u
XHW6NUAmullw6UEeHCKYxTSH
=+39E
-----END PGP SIGNATURE-----
Nov 13 '05 #2
I have a statement in VB to execute that sql.

strSQLUpdate="UPDATE TableTest SET STATUS = "A" WHERE (Code <> "X") AND
(Code <> "J") ORDER BY DOB; "

DB.Execute strSQLUpdate

But even if I run it from the SQL view of a Query, it doesn't work.

I understand that I don't need an Order By for the update. The way the
form works, if a user has sorted their data, and then wants to update a
field's value, the update sql that is built by the form uses the entire
sql behind the form. And like I said, it works in Access 97. So, what
is new and improved in Access 2003 that makes this query not runnable?

Nov 13 '05 #3
Erika,
Forgive me if I add my two cents when I am not sure my suggestion is any
better than MGFoster. Ditto on not needing the ORDER BY clause. Will this
work?
UPDATE TABLETEST SET STATUS = 'A' WHERE CODE NOT IN ('X','J');
I found a difference in the use of quotes in Access 2002 in some cases. SQL
Server uses single quotes to delimit text and Jet uses both double-quotes
and single quotes. There is an option in 2002 to enforce SQL Server syntax
rules. If this is enabled then double-quotes around text begin to cause a
problem. I rewrote your update statement with single-quotes thinking that
this may be part of the issue. Let me know if I am on the right track.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"erika" <mi******@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
I am trying to run the following update query in code.

UPDATE TableTest SET STATUS = "A" WHERE (Code <> "X") AND (Code <>
"J") ORDER BY DOB;

I am getting an error, "Missing Semicolon (;) at end of SQL statement".
Please tell me what is wrong with the syntax. It worked in Access 97.
But in Access 2003, it is so fussy!

I appreciate your help. Thanks, Erika

Nov 13 '05 #4
Thank you both. It was the ORDER BY. This sequence of button clicks
must not have been tested in my '97 program, because it fails there,
too, and I never knew it. I appreciate your time and patience. Erika

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jannordgreen | last post by:
I am using Winxp and Ms Access 2003. I get this error message: error 3137, Missing semicolon (;) at end of SQL statement when I run this code: Dim I As Integer For I = 0 To...
42
by: Prashanth Badabagni | last post by:
Hi, Can any body tell me how to print "hello,world" with out using semicolon Thanks in advance .. Bye Prashanth Badabagni
3
by: vasudevmukherjee | last post by:
Hi! Anybody there who can answer me on my following code: When I try to compile this code, I get message of a missing ']' on the line 3 i.e. int a;, why is this happening Thanks in...
1
by: kalyan. | last post by:
how do i overcome this problem ?? Thanku in Advance .
3
by: Ken | last post by:
Hi all, I want to printf a sentence with a semicolon, for examples: printf(" I like C language; You like C++ language."); But C compiler alway identify the semicolon as a end of a sentence and...
4
by: liz0001 | last post by:
Hi, I am getting this error when I try to run my code: Missing semicolon (;) at end of SQL statement However, there is a semicolon at the end of my statement. Here is the statement: INSERT...
2
by: P2P | last post by:
Hi all, I build my project on VS 2005 and get this error: c:\dx90sdk\include\strmif.h(27392) : error C2146: syntax error : missing ';' before identifier 'lpSurf' can you help me how to fix...
2
by: im2cre8iv | last post by:
Here is my code where I am receiving the error: #include <fstream> #include "BinaryTree.h" using namespace std; Node* BinaryTree::MakeTree(ifstream& infile) { char name; infile>>name;
1
by: radhikabece | last post by:
I m writhing the code as below in the button click event: Dim count As String = "select max(CutomerId) from CustomerInfo" cn.Open() cmd = New OleDbCommand(count, cn) ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.