Missing Semicolon error

[erika]

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

[MGFoster]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQmaWX4echKq OuFEgEQI6qACglh tCE6ceGz5xtXf8c g4NJaK7XGMAn3+u
XHW6NUAmullw6UE eHCKYxTSH
=+39E
-----END PGP SIGNATURE-----

[erika]

I have a statement in VB to execute that sql.

strSQLUpdate="U PDATE 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?

[Alan Webb]

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*******@SPAMh otmail.com
"It's not IT, it's IS"

"erika" <mi******@gmail .com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.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

[erika]

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