By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,390 Members | 1,550 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,390 IT Pros & Developers. It's quick & easy.

Missing Semicolon error

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.