Request For List Of Reserved Words and Arguement Name in Writing SQL Statement

[victoria_1018]

Hi,
I am working on my search engin and encounter an error like this

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

/TechnicalReport/Completed/SearchVerifiedWorksOrderReportResult.asp, line 99


Can I just check if anyone can provided me with a list of reserved word or arguement name in writing a SQl statement as I believe there isn't any misspelled, missing or the punctuation error in my coding.

Thanks

Regards
Victoria

[glenngv]

I don't have a list but can we take a look at the SQL statement?

[victoria_1018]

Hi,
I have attached the script.
Thank You

[glenngv]

you should have posted just the SQL statement in question. That's a long code to view. What you can do is put:

response.write SQL & "<br>"

BEFORE executing the SQL, then post the output statement in this thread. That's easier to debug.

[victoria_1018]

Oh Sorry, I have re-post the codes. These codes only consist the areas that are related to the SQL Statement.
I thought that If I were tobpost the whole script, you will have a better understanding on how my SQL statement run.
Regards
Victoria

[glenngv]

But it is still better to see the actual SQL statement formed since you are creating it dynamically. Please post the generated SQL statement.

BTW, is report_id numeric or varchar? why did you enclose it in single quotes?

" where report_ID >'" & iLastID & "'"

[victoria_1018]

report_ID is varchar, the format is something like W00001.
BTW, is report_id numeric or varchar? why did you enclose it in single quotes?

I have re-submit the code, this time only consist og the SQL statement related areas.

Thank You

Regards
Victoria

[raf]

Answer to original question.
Reserved words for JetSQL (Microsoft databases)
By the looks of it, it's even bigger then my english vocabulary

Start quote Access Helpfunction----------------------------

The following list includes all words reserved by the Microsoft Jet database engine for use in SQL statements. The words in the list that are not in all uppercase letters are also reserved by other applications. Consequently, the individual Help topics for these words provide general descriptions that do not focus on SQL usage.


--------------------------------------------------------------------------------

Note Words followed by an asterisk (*) are reserved but currently have no meaning in the context of a Microsoft® Jet SQL statement (for example, Level and TableID). Words that are not underlined do not have linked explanations.


--------------------------------------------------------------------------------

A

ABSOLUTE ANY
ADD ARE
ADMINDB AS
ALL ASC
Alphanumeric — See TEXT ASSERTION
ALTER AUTHORIZATION
ALTER TABLE AUTOINCREMENT — See COUNTER
And Avg
AS


B-C

BEGIN COLLATION
Between COLUMN
BINARY COMMIT
BIT COMP, COMPRESSION
BIT_LENGTH CONNECT
BOOLEAN — See BIT CONNECTION
BOTH CONSTRAINT, CONSTRAINTS
BY CONTAINER
BYTE CONTAINS
CASCADE CONVERT
CATALOG Count
CHAR, CHARACTER — See TEXT COUNTER
CHAR_LENGTH CREATE
CHARACTER_LENGTH CURRENCY
CHECK CURRENT_DATE
CLOSE CURRENT_TIME
CLUSTERED CURRENT_TIMESTAMP
COALESCE CURRENT_USER
COLLATE CURSOR


D

DATABASE DISALLOW
DATE — See DATETIME DISCONNECT
DATETIME DISTINCT
DAY DISTINCTROW
DEC, DECIMAL DOMAIN
DECLARE DOUBLE
DELETE DROP
DESC


E-H

Eqv FOREIGN
EXCLUSIVECONNECT FROM
EXEC, EXECUTE FROM Clause
EXISTS GENERAL — See LONGBINARY
EXTRACT GRANT
FALSE GROUP
FETCH GUID
FIRST HAVING
FLOAT, FLOAT8 — See DOUBLE HOUR
FLOAT4 — See SINGLE


I

IDENTITY INPUT
IEEEDOUBLE — See DOUBLE INSENSITIVE
IEEESINGLE — See SINGLE INSERT
IGNORE INSERT INTO
IMAGE INT, INTEGER, INTEGER4 — See LONG
Imp INTEGER1 — See BYTE
In INTEGER2 — See SHORT
IN INTERVAL
INDEX INTO
INDEXCREATEDB Is
INNER ISOLATION


J-M

JOIN LONGTEXT
KEY LOWER
LANGUAGE MATCH
LAST Max
LEFT MEMO — See LONGTEXT
Level* Min
Like MINUTE
LOGICAL, LOGICAL1 — See BIT Mod
LONG MONEY — See CURRENCY
LONGBINARY MONTH
LONGCHAR


N-P

NATIONAL Outer*
NCHAR OUTPUT
NONCLUSTERED OWNERACCESS
Not PAD
NTEXT PARAMETERS
NULL PARTIAL
NUMBER — See DOUBLE PASSWORD
NUMERIC — See DECIMAL PERCENT
NVARCHAR PIVOT
OCTET_LENGTH POSITION
OLEOBJECT — See LONGBINARY PRECISION
ON PREPARE
OPEN PRIMARY
OPTION PRIVILEGES
Or PROC, PROCEDURE
ORDER PUBLIC


Q-S

REAL — See SINGLE SMALLDATETIME
REFERENCES SMALLINT — See SHORT
RESTRICT SMALLMONEY
REVOKE SOME
RIGHT SPACE
ROLLBACK SQL
SCHEMA SQLCODE, SQLERROR, SQLSTATE
SECOND StDev
SELECT StDevP
SELECTSCHEMA STRING — See TEXT
SELECTSECURITY SUBSTRING
SET Sum
SHORT SYSNAME
SINGLE SYSTEM_USER
SIZE


T-Z

TABLE UPDATEOWNER
TableID* UPDATESECURITY
TEMPORARY UPPER
TEXT USAGE
TIME — See DATETIME USER
TIMESTAMP USING
TIMEZONE_HOUR VALUE
TIMEZONE_MINUTE VALUES
TINYINT Var
TO VARBINARY — See BINARY
TOP VARCHAR — See TEXT
TRAILING VarP
TRANSACTION VARYING
TRANSFORM VIEW
TRANSLATE WHEN
TRANSLATION WHENEVER
TRIM WHERE
TRUE WITH
UNION WORK
UNIQUE Xor
UNIQUEIDENTIFIER YEAR
UNKNOWN YESNO — See BIT
UPDATE ZONE
UPDATEIDENTITY