Hello,

I cannot see the Error that Access is giving me.  If anybody can see it, 
please let me know.

The error is:
The SELECT statement includes a reserved word or an argument name that is 
misspelled or missing, or the punctuation is incorrect.
Code: 80004005
Source: Microsoft JET database engine

The query:
select distinct program, type, subtype from ProgTypes union select 
distinct program, NULL as type, NULL as subtype from OtherProg

I put the SQL in a Microsoft Access Query and it works, but when VBScript 
opens it in a recordset, I get an error.  I am completely baffled.

Thanks,

Brian Mains

I'm not sure but the word "Type" might be causing the problem as it is a
reserved word. Again, I don't know if this will work but try putting the
field names in brackets [Type] so that VB understands it is field name.
This may cure the problem. Otherwise, you will have to change the field
name to something other than Type.

Richard Lobel
Accessible Data
3805 S. 316th Street
Auburn, WA 98001-3109
richard@a...
Tel:   (xxx) xxx-xxxx
Fax:  (xxx) xxx-xxxx


****original message****
The error is:
The SELECT statement includes a reserved word or an argument name that
is 
misspelled or missing, or the punctuation is incorrect.
Code: 80004005
Source: Microsoft JET database engine

The query:
select distinct program, type, subtype from ProgTypes union select 
distinct program, NULL as type, NULL as subtype from OtherProg

<<The error is:
The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.
Code: 80004005
Source: Microsoft JET database engine

The query:
select distinct program, type, subtype from ProgTypes union select
distinct program, NULL as type, NULL as subtype from OtherProg>>

The clue is in RESERVED word - I think your use of the word TYPE may be the
problem.

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Hi Brian

I've had this problem when one of my field names is actually a reserved word
in VB.

I think "type" is such a word





Regards

Richard Gibson
IT Manager


The information in this message is confidential and intended for the
addressee only.  If you have received this message in error please delete
and notify the sender, any other action may be unlawful.

The views expressed in this message are personal and not necessarily those
of Walkerpack Ltd unless explicitly stated.


-----Original Message-----
From: bmains@s... [mailto:bmains@s...]
Sent: 25 April 2002 16:54
To: Access
Subject: [access] Error with Access in Select Statement


Hello,

I cannot see the Error that Access is giving me.  If anybody can see it, 
please let me know.

The error is:
The SELECT statement includes a reserved word or an argument name that is 
misspelled or missing, or the punctuation is incorrect.
Code: 80004005
Source: Microsoft JET database engine

The query:
select distinct program, type, subtype from ProgTypes union select 
distinct program, NULL as type, NULL as subtype from OtherProg

I put the SQL in a Microsoft Access Query and it works, but when VBScript 
opens it in a recordset, I get an error.  I am completely baffled.

Thanks,

Brian Mains

Hello,

I tried putting the "[]" around the words type and subtype and i still get 
the error.  Anything else that you may think of?

Thanks again,

Brian Mains

Brian,

    I would try including the table name in the select clause (e.g.
[progtypes.type]). Your query is a union. How does each part of the union
execute separately?

    I also tried a similar query in my database and it worked fine. I am
using access 97. What version are you using?

Barry


----- Original Message -----
From: "Brian Mains" <bmains@s...>
To: "Access" <access@p...>
Sent: Thursday, April 25, 2002 1:12 PM
Subject: [access] Re: Error with Access in Select Statement


> Hello,
>
> I tried putting the "[]" around the words type and subtype and i still get
> the error.  Anything else that you may think of?
>
> Thanks again,
>
> Brian Mains
>

Brian -

I think that at least the field named "type"
might be a problem as it is a reserved word.

It took me a while but I found the following
listing of reserved words in access97.

Dave

[bewhere of wordwrapping]

From http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q109312


These words are reserved in all versions except where noted otherwise. Because it is not practical to provide a list of all reserved
words, such as built-in function names or Microsoft Access user-defined names, please check your product documentation for
additional reserved words. Note that if you set a reference to a type library, an object library, or an ActiveX control, that
library's reserved words are also reserved words in your database. For example, if you add an ActiveX control to a form, a reference
is set and the names of the objects, methods, and properties of that control become reserved words in your database. 



-A
    ADD
    ALL (2.0, 7.0, 97)
    Alphanumeric
    ALTER
    AND
    ANY (2.0, 7.0, 97)
    Application
    AS
    ASC
    Assistant
    AUTOINCREMENT
    Avg
-B
    BETWEEN
    BINARY
    BIT
    BOOLEAN
    BY
    BYTE
-C
    CHAR, CHARACTER
    COLUMN
    CompactDatabase (2.0, 7.0, 97)
    CONSTRAINT
    Container  (2.0, 7.0, 97)
    Count
    COUNTER (2.0, 7.0, 97)
    CREATE
    CreateDatabase  (2.0, 7.0, 97)
    CreateField (2.0, 7.0, 97)
    CreateGroup (2.0, 7.0, 97)
    CreateIndex (2.0, 7.0, 97)
    CreateObject  (2.0, 7.0, 97)
    CreateProperty (2.0, 7.0, 97)
    CreateRelation (2.0, 7.0, 97)
    CreateTableDef (2.0, 7.0, 97)
    CreateUser (2.0, 7.0, 97)
    CreateWorkspace (2.0, 7.0, 97)
    CURRENCY
    CurrentUser (2.0, 7.0, 97)
-D
    DATABASE (2.0, 7.0, 97)
    DATE (2.0, 7.0, 97)
    DATETIME
    DELETE
    DESC
    Description
    DISALLOW
    DISTINCT
    DISTINCTROW
    Document (2.0, 7.0, 97)
    DOUBLE (2.0, 7.0, 97)
    DROP
-E
    Echo (2.0, 7.0, 97)
    Else
    End
    Eqv
    Error
    EXISTS (2.0, 7.0, 97)
    Exit
-F
    FALSE
    Field, Fields (2.0, 7.0, 97)
    FillCache (2.0, 7.0, 97)
    FLOAT, FLOAT4, FLOAT8
    FOREIGN (2.0, 7.0, 97)
    Form, Forms
    FROM
    Full (2.0, 7.0, 97)
    FUNCTION
-G
    GENERAL
    GetObject (2.0, 7.0, 97)
    GetOption (2.0, 7.0, 97)
    GotoPage
    GROUP  (2.0, 7.0, 97)
    GROUP BY
    GUID (7.0, 97)
-H
    HAVING
-I
    Idle (2.0, 7.0, 97)
    IEEEDOUBLE, IEEESINGLE
    If
    IGNORE (2.0, 7.0, 97)
    Imp
    IN, In
    INDEX
    Index, Indexes (2.0, 7.0, 97)
    INNER
    INSERT
    InsertText (2.0, 7.0, 97)
    INT, INTEGER, INTEGER1, INTEGER2, INTEGER4
    INTO
    IS, Is
-J
    JOIN
-K
    KEY (2.0, 7.0, 97)
-L
    LEFT
    Level
    Like
    LOGICAL, LOGICAL1
    LONG, LONGBINARY, LONGTEXT
-M
    Macro
    Match (2.0, 7.0, 97)
    Max, Min, Mod
    MEMO
    Module
    MONEY
    Move (2.0, 7.0, 97)
-N
    NAME (2.0, 7.0, 97)
    NewPassword (2.0, 7.0, 97)
    NO
    Not
    NULL
    NUMBER, NUMERIC
-O
    Object (2.0, 7.0, 97)
    OLEOBJECT (2.0, 7.0, 97)
    OFF
    ON
    OpenRecordset (2.0, 7.0, 97)
    OPTION
    OR, Or
    ORDER
    Outer (2.0, 7.0, 97)
    OWNERACCESS
-P
    Parameter (2.0, 7.0, 97)
    PARAMETERS
    Partial (2.0, 7.0, 97)
    PERCENT (2.0, 7.0, 97)
    PIVOT
    PRIMARY
    PROCEDURE
    Property (2.0, 7.0, 97)
-Q
    Queries
    Query
    Quit (2.0, 7.0, 97)
-R
    REAL
    Recalc (2.0, 7.0, 97)
    Recordset (2.0, 7.0, 97)
    REFERENCES
    Refresh
    RefreshLink (2.0, 7.0, 97)
    RegisterDatabase (2.0, 7.0, 97)
    Relation (2.0, 7.0, 97)
    Repaint (2.0, 7.0, 97)
    RepairDatabase (2.0, 7.0, 97)
    Report
    Reports
    Requery
    RIGHT
-S
    SCREEN
    SECTION
    SELECT (2.0, 7.0, 97)
    SET
    SetFocus (2.0, 7.0, 97)
    SetOption (2.0, 7.0, 97)
    SHORT
    SINGLE (2.0, 7.0, 97)
    SMALLINT (2.0, 7.0, 97)
    SOME (2.0, 7.0, 97)
    SQL
    StDev, StDevP
    STRING
    Sum
-T
    TABLE
    TableDef, TableDefs (2.0, 7.0, 97)
    TableID
    TEXT
    TIME, TIMESTAMP (2.0, 7.0, 97)
    TOP (2.0, 7.0, 97)
    TRANSFORM
    TRUE
    Type
-U
    UNION
    UNIQUE
    UPDATE
-V
    VALUE
    VALUES (2.0, 7.0, 97)
    Var, VarP
    VARBINARY, VARCHAR (2.0, 7.0, 97)
-W
    WHERE
    WITH
    Workspace (2.0, 7.0, 97)
-X
    Xor
-Y
    Year
    YES
    YESNO (2.0, 7.0, 97)

The following symbols should not be used as part of a field name: 

   .
   / 
   *
   :
   !
   #
   &
   -





> 
> From: bmains@s...
> Date: 2002/04/25 Thu PM 03:54:26 EDT
> To: "Access" <access@p...>
> Subject: [access] Error with Access in Select Statement
> 
> Hello,
> 
> I cannot see the Error that Access is giving me.  If anybody can see it, 
> please let me know.
> 
> The error is:
> The SELECT statement includes a reserved word or an argument name that is 
> misspelled or missing, or the punctuation is incorrect.
> Code: 80004005
> Source: Microsoft JET database engine
> 
> The query:
> select distinct program, type, subtype from ProgTypes union select 
> distinct program, NULL as type, NULL as subtype from OtherProg
> 
> I put the SQL in a Microsoft Access Query and it works, but when VBScript 
> opens it in a recordset, I get an error.  I am completely baffled.
> 
> Thanks,
> 
> Brian Mains
> 

Hello,

The version of Access I am using is 2000, and it worked in mine too.  It 
works, but for some reason in the Windows Script Host, I still get an 
error whenever I open up the query in the recordset.  It is the weirdest 
thing.  I even put the table names in, and I still get the error.  Any 
other ideas?

Thanks,

Brian Mains